Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Homework Help: Amortization table question

  1. Mar 13, 2005 #1
    Amortization table question plz help

    Vanna has just financed the purchase of a home for $200 000. She agreed to repay the loan by making equal monthly blended payments of $1500 each at 9%/a compounded monthly

    I need to create an amortization table using excel, I think I can do that following the example problem, but for this question I dont understand what they mean by blended payments what is the interest rate going to be? Also how will I figure out 1)how much sooner the loan would be paid off if she made a 15% down payment? 2) How much would Vanna have saved if she had obtained a loan at 7%/a compounded monthly?

    DO I HAVE TO MAKE 3 amortization tables to answer all these questions or is there an easier way? PLZ HELP
  2. jcsd
  3. Mar 13, 2005 #2

    Andrew Mason

    User Avatar
    Science Advisor
    Homework Helper

    The blended payment is a fixed payment that includes interest and principal. The proportion of interest to principal decreases as the loan gets paid. Set up a spreadsheet with a column for interest and one for principal.

    The interest rate each month would be 1/12 of 9% or 9/1200. (The compounding does not occur if the payment is made. Only if the payment is not made is the interest is added to principal and interest accrues on it).

    Work out the interest each month. Ordinarily that would cover all the interest and you would use the remainder to pay down principal. But in this example, the loan interest is $1500 per month. So this is a poor question. Complain to your teacher. It is not a blended payment.

    This just reduces the initial principal. You start with an initial principal amount of $170,000. At least that principal can be paid down with a montly payment of $1500.

    This requires another spread sheet. However in this one, the interest each month is added at a rate of 7/1200 x principal. At $1500 per month, it will be paid sooner. The difference would be the number of months sooner x $1500 which, because the 9% loan will never be paid is infinity.

    The best way to compare the two loans is to take the same amortization period and compare the monthly payments. But that requires a complicated formula or a computer program. For $200,000 at 7% amortized over 300 months the loan works out to $1413.56 per month. For 9% is works out to $1678.39 so she would save the difference x $300.

    Last edited: Mar 14, 2005
  4. Mar 13, 2005 #3
    Here are the headings

    Payment#/ Periodic Payment/ Interest Paid/ Principal Paid/ Outstanding Princip

    If all these heading are in cell 1 (ex A1, B1, C1, etc) for this question what formula will I put in the interest paid cell C2? i= Outstanding balance*?? what is the rate I input here? How much will the payment number go up to?
  5. Mar 13, 2005 #4

    Andrew Mason

    User Avatar
    Science Advisor
    Homework Helper

    for c2 use: e1*.09/12 (interest paid)

    for d2 use: b2-c2 (principal paid)

    for e2 use: e1-d2 (outstanding principal balance)

  6. Mar 14, 2005 #5
    I did everything Andrew said But I want to know how many payments will there we how do I know how far to extend my table? I entered all those formulas in the correct spot but when I use the fill down command to fill the table in interest paid it stays $1500 until cell 25 and then starts increasing same with outstanding principal instead of getting smaller its getting bigger same with the principal paid it stays the same $0.00 until 25 cell What am I doing wrong? :cry:
  7. Mar 14, 2005 #6

    Andrew Mason

    User Avatar
    Science Advisor
    Homework Helper

    Nothing. Blame your teacher who gave you the problem. For $200,000 at 9% you will never pay it down with a $1500 monthly payment. The principal does not decrease - it just covers the interest. So payments go forever. For 7% you will need about 259 lines.

    The number of months to pay it down is:

    [tex]N_m = -\frac{ln(1-(P/A)(i/12))}{ln(1+(i/12))}[/tex]

    i = interest rate
    P = principal
    A = amount of each payment
    N_m = number of months

  8. Mar 14, 2005 #7
    What is that ln before the brackets? Where did u get this formula?
    My question say
    b) how long will it take to repay the loan
    c)How much will be the final payment
    d)Determine how much interest she will pay for her loan
    e)Graph amortization of the loan (hint:eek:utstanding principal vs month)
    f)How much sooner would the loan be paid if she made a 15% down payment?
    g)How much would vanna have saved if she had obtained a loan 7%/a compounded monthly?

    HOLY MOLY HOw do I answer all these question if I can even get the table right ahhhhhhhhhh
    By the way I did email my teacher for help :cry:
  9. Mar 14, 2005 #8

    Andrew Mason

    User Avatar
    Science Advisor
    Homework Helper

    ln is natural logarithm. You'll find it on your calculator.


    There is no final payment. It goes on forever.
    She will pay an ever increasing amount of interest. $1500 more every month.
    It is a flat horizontal line. The principal stays at 200k
    Use the formula to find out how long it would take. Or work it out on the spread sheet. But you can't answer the question because the first loan never gets paid.
    You can't answer this question because she would pay an infinite amount at 9%.

    You got the table right. Your frustration is the result of a poorly designed problem.

  10. Mar 14, 2005 #9
    lol I think I am going to take your last post and email it to the principal if the teacher thinks its possible to do thanks soo much lol the whole assignment is like this all of the questions make no sense havent u seen my other post on the lottery with two payment options? I dont know what the teacher was thinking when she made this assignment, must have been sleeping :yuck:
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook