How to Create an Amortization Table for a Mortgage Loan with Blended Payments

[aisha]

Amortization table question please 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 don't 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

 

[Andrew Mason]

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 don't understand what they mean by blended payments what is the interest rate going to be?

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.

how much sooner the loan would be paid off if she made a 15% down 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.

2) How much would Vanna have saved if she had obtained a loan at 7%/a compounded monthly?

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.

AM

 

[aisha]

Here are the headings

Payment#/ Periodic Payment/ Interest Paid/ Principal Paid/ Outstanding Princip
1...1500
2...1500
3...1500
4
5

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?

 

[Andrew Mason]

Here are the headings

Payment#/ Periodic Payment/ Interest Paid/ Principal Paid/ Outstanding Princip
1...1500
2...1500
3...1500
4
5

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

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

for d2 use: b2-c2 (principal paid)

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

AM

 

[aisha]

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?

 

[Andrew Mason]

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?

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:

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

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

AM

 

[aisha]

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

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

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 (hintutstanding 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

 

[Andrew Mason]

What is that ln before the brackets? Where did u get this formula?

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

My question say
b) how long will it take to repay the loan

Forever.

c)How much will be the final payment

There is no final payment. It goes on forever.

d)Determine how much interest she will pay for her loan

She will pay an ever increasing amount of interest. $1500 more every month.

e)Graph amortization of the loan (hintutstanding principal vs month)

It is a flat horizontal line. The principal stays at 200k

f)How much sooner would the loan be paid if she made a 15% down payment?

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.

g)How much would vanna have saved if she had obtained a loan 7%/a compounded monthly?

You can't answer this question because she would pay an infinite amount at 9%.

HOLY MOLY HOw do I answer all these question if I can even get the table right ahhhhhhhhhh

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

AM

 

[aisha]

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 haven't u seen my other post on the lottery with two payment options? I don't know what the teacher was thinking when she made this assignment, must have been sleeping