Excel Question

  • kuroshiro

    Posts: 786

    Sep 20, 2011 7:06 PM GMT
    Okay guys, I've been trying to find the answer to this but I can't... hopefully someone can assist me.

    I'm redesigning my debt chart and everything, but instead of constantly editing my charts with the deductions I wanted to simplify everything. Here's what I'm trying to do:

    I have a "Total Amount" column. Next to it I have a "Last Payment" column. What I wanted to do was the last payment to deduct from the total amount column, however, I want it to continually update every time I put a figure in... and not just... keep the same total with varying deductions applied.

    Halp? 0_o (Did that make any sense anyways?)
  • MSUBioNerd

    Posts: 1813

    Sep 20, 2011 8:53 PM GMT
    I'm having trouble parsing what exactly it is you want here. From the best of my understanding of your question:

    1) You currently have a column which adds up the total amount owed
    2) You have another column which records the last payment

    You want to subtract the value in 2 from the value in 1, and not have to type in anything to make it update?

    If this is the case, go to a cell in a third column. Type "= A1 - B1" without the quotation marks, where A1 is the cell with the current amount owed, and B1 is the cell with the last payment. This will give you the new amount owed.


    A different possible interpretation is that you have some current amount of money owed, and you know what interest rate it's accumulating at. For this, you can create a simple amortization table. Here's what you do.

    In cell A1, type the current amount owed.
    In cell B1, type "=A1*2.718281828459045^(r/12)" where r is the yearly percentage interest rate.
    In cell C1, type whatever you plan to pay for that month.
    In cell D1, type "=B1-C1"
    In cell A2, type "=D1"
    copy cells B1, C1, and D1
    paste them into cells B2, C2, and D2.
    Copy all of row 2, and paste it into row 3. Repeat this for as many months as you need to make payments. Each row will have column A showing how much you owed at the end of the previous month, Column B showing how much you owe with interest added, Column C showing what you pay that month (you can vary this if needed), and column D showing what you owe at the end of that month.

    You can also set this up to show explicitly how much of your payment is going to interest and how much is going toward the principal:
    In cell F1, type "=B1-A1"
    In cell G1, type "=C1-F1"
    Copy and paste these cells into whatever row you need. Column F shows how much of the payment went to interest, and column G shows how much goes to the principal.

    (In all cases, don't type the quotation marks)

    Is this what you were looking for? If not, could you be clearer in what you'd like?
  • Posted by a hidden member.
    Log in to view his profile

    Sep 20, 2011 9:12 PM GMT
    The solution is one word: "Quicken."
    In the time it takes you to figure out your question, figure out the answer, create the worksheet and formulas... you could have earned enough money to pay for Quicken. Or Mint online is free.
  • kuroshiro

    Posts: 786

    Sep 21, 2011 2:28 AM GMT
    I figured I wasn't very clear with what I asked. Basically, whenever I put an amount into the "Last Payment" category, I want it deducted from the Total Amount category...


    If I start out with $100.00 in the Total Amount category and $50.00 in the Last Payment category, this would denote my previous Total Amount was $150.00. So, when I change the total to say... $10.00 one week, it would drop to $90 and say $45 another week it would change to $45. Basically, I want the number to update every time I change the number in the Last Payment category without just being a simple deduction equation (one box minus the other).
  • MSUBioNerd

    Posts: 1813

    Sep 21, 2011 5:15 AM GMT
    Still not entirely clear how you're trying to set things up here (given that I think you might be using column in place for cell at at least one point), but here's my guess on the latest clarification:

    I'm guessing you have a particular cell in your spreadsheet that tells you what your current amount is. You have a column that shows the payments you've made toward it.

    Option A ) If you simply want a single cell to display how much you currently owe:

    1 ) Pick where you want the current total amount owed to be. For example purposes, I'm going to say it should be cell G1.

    2 ) Pick another cell where you'll display the total amount you've paid to be. For this example, I'm calling it cell F1.

    3 ) In cell F1, set it up to sum the entire column of where you're entering all of your payments. So, for example, if you're sticking your payments in column D, type "=sum(D1icon_biggrin.gif1000)" in cell F1. Make the sum start at the first row you've entered a payment, but have it go well past where you've entered thus far (just as long as the extra cells are currently blank). This will update automatically when you enter new values in the payment column.

    4 ) Subtract F1 from the cell that already shows how much you owe. So, if that value is in cell B37, you'd type in G1 "=B37-F1".

    This will make the amount you've paid and the amount you still owe update automatically every time you enter in a new payment.

    Option B ) If you want each row to show you how much you owe after the payment you've just entered

    1 ) How much you currently owe must be somewhere. Let's say for the moment it's in cell B1.

    2 ) You're going to enter you payments somewhere. Let's say you'll stick them in column C. The value in C1 will be first payment, the value in C2 will be the next payment, etc.

    3) In cell B2, type "B1-B2"

    4) Copy cell B1, and paste into the full column of B. You can highlight a whole ton of cells at once, and paste across lots of them.

    By doing this, each row of B will show you the amount owed before the payment you entered in that row of C. There will be one value of B where there is not value of C. That will be the current total amount owed.

    Since I'm still doing some guess work of what it is you want, feel free to send me a direct message. If you can be clearer, and possibly send me an example spreadsheet so you can be more specific of what you're looking for, it'll make my responses more useful.