ORTIZATION TABLEMonthly Mortgage Payment Calculator

[aisha]

The current interest rate is 8.7%/a interest is compounded semi-annually and calculated monthly. The couple that is borrowing $90 000 for their new home wants to know how much it will cost them each month to pay off the loan. Assume they will make one payment per month. Calculate the monthly payment?

Do I have to find the nominal interest rate for this question because this is not a simple annuity? The compounding period is semi-annually but the payment period is monthly. Will I be using the present or future value to find the monthly payment? Is $90 000 present? Can someone set this question up for me thanks

What does n=? the converstion period (2) times what?
oh in the first quetion it said they will need to borrow $90000 for the home with an amortization period of 25 years I think this multiplied by 2 will give n.

In my graphing calculator I entered

N=50
I=8.7
Present Value= 90 000
PMT=0
Future Value=0
payment per year=12
compounded per year=2

when i pressed alpha solve for PMT (Payment) I got -2145.80 as my answer I am not sure if this is correct.

 

[aisha]

Someone must be able to help me with this question common please some1

 

[OptimusPrime]

Be patient.

 

[joejo]

yes the graphing calculator will give you a negative just write the # w/o the negative!

 

[aisha]

yes I know to write the answer without the negative but I am not sure if the value is right and how to get it without the graphing calculator, I tried to find the nominal interest rate if that is needed it was 8.546%/a compounded monthly is equivalent to 8.7%/a compounded semi-annually. I am trying to be really patient please help me out thanks

 

[aisha]

I changed my numbers a little and got a little more reasonable monthly payment

n=300
I=8.7%
PV=90 000
PMT=0
FV=0
P/Y=12
C/Y=2

When i pressed alpha solve on PMT i got my value to be -$727.52 Can someone help me out?

 

[The Bob]

As I see it they need to pay off the interest each month and then some of the $90 000. If the interest is split over the 6 months that the compound interest is in then it is still as simple.

The Bob (2004 ©)

 

[shmoe]

Why didn't you use your 8.546% interest as the monthly rate? They said the interest was calculated monthly.

 

[Andrew Mason]

I changed my numbers a little and got a little more reasonable monthly payment

n=300
I=8.7%
PV=90 000
PMT=0
FV=0
P/Y=12
C/Y=2

When i pressed alpha solve on PMT i got my value to be -$727.52 Can someone help me out?

This is not a trivial calculation.

First of all, figure out the effective interest rate which is the interest rate that you use to multiply the principal by to get the interest payable.

$$i_e = np((1 + \frac{i_n}{c})^\frac{1}{pc} - 1)$$

where: i_e is the effective annual interest rate
i_n is the annual nominal rate of interest
c is the number of compounding periods per year
pc is the number of payments per compounding period
np is the number of payments per year


So for semi-annual compounding and monthly payment:

$$i_e = 12((1 + \frac{8.7}{2})^{1/6} - 1)$$

$$i_e = 12(1.0435^{1/6} - 1) = 8.546$$ per cent per annum

Multiply the outstanding principal by 1/12 of that interest rate each month to get the interest owed.

The actual payment will be $727.52/month to be paid off in 300 months (25 years).

AM

 

[aisha]

ok I think I understand that this is not a simple annuity but a general annuity therefore we have to find the nominal interest rate which was 8.546% compounded monthly is equivalent to 8.7% compounded semi-annually.

Andrew you did this but I don't understand how my answer is right then because in my post when i entered the values into the graphing calculator I forgot to enter the nominal interest rate found and used 8.7% instead.

The actual payment will be $727.52/month to be paid off in 300 months (25 years). This is the answer I got but it can't be right both of us didnt use the nominal interest rate if we didnt have to use it then why did we find it?

 

[Andrew Mason]

ok I think I understand that this is not a simple annuity but a general annuity therefore we have to find the nominal interest rate which was 8.546% compounded monthly is equivalent to 8.7% compounded semi-annually.

Andrew you did this but I don't understand how my answer is right then because in my post when i entered the values into the graphing calculator I forgot to enter the nominal interest rate found and used 8.7% instead.

The actual payment will be $727.52/month to be paid off in 300 months (25 years). This is the answer I got but it can't be right both of us didnt use the nominal interest rate if we didnt have to use it then why did we find it?

If you want to figure out how much interest you have accrued and paid with each monthly payment, you have to use the effective interest rate from the nominal rate. That is what I showed you. That is also what your program does.

AM

 

[aisha]

oh yes now i remember thanks the graphing calculator finds the nominal rate itself but if you were to do this using the present formula i'd have to plug in the nominal interest rate. Thanks so much

 

[Andrew Mason]

oh yes now i remember thanks the graphing calculator finds the nominal rate itself but if you were to do this using the future formula i'd have to plug in the nominal interest rate. Thanks so much, oh for this question I do use the future value formula right?

I don't know what your future value formula gives you but I am not sure why you would want to use it. This is an amortization question. The amortization would give you the cumulative value of all the payments up to a certain time in the future. Is that your definition of future value?

To me, the future value would be the value at a time in the future of a present capital sum invested at a certain rate and compounding period with no payments until maturity. But that may not be your definition.

AM

 

[aisha]

sorry I edited my post i meant present not future. I don't if I am doing the next part right, my next question said create an amortization table for the first 5 years of the mortgage, and include a graph showing the declining balance at the end of each year.

Here is what I have in excel
A>>>>>>>>>>B>>>>>>>>C>>>>>>>>>>D>>>>>>>>>>>>>>>>E
Payment#...Payment...Interest Paid..Principal Paid... Outstanding Balance
0...............90000
1....727.52...E2*0.007121667...=b3-c3....93,118.18
2....727.52...3482.00...-3,114.48...93,027.91
3
4
5

The oustanding balance is not decreasing for some reason is there something wrong with the question? I used the nominal interest rate 8.546% divided by 12 because of the monthly payment. HELP! Even the principal paid is negative for some reason. payment 5 is in cell A7

 

[Andrew Mason]

sorry I edited my post i meant present not future. I don't if I am doing the next part right, my next question said create an amortization table for the first 5 years of the mortgage, and include a graph showing the declining balance at the end of each year.

The oustanding balance is not decreasing for some reason is there something wrong with the question? I used the nominal interest rate 8.546% divided by 12 because of the monthly payment. HELP! Even the principal paid is negative for some reason. payment 5 is in cell A7

You are using E2 in column C. It should be E0. The spreadsheet should look like this:

A>>>>>>>>>>B>>>>>>>>C>>>>>>>>>>D>>>>>>>>>>>>>>>>E
Payment#...Payment...Interest Paid..Principal Paid... Outstanding Balance
0...............90000
1....727.52...E0*0.007121667...=b3-c3....89,913.46
2....727.52...640.36... 87.16... 89,826.30
3
4
5

AM

 

[aisha]

ok in my table that i made
my heading are in the A1, B1, C1, D1 and E1
the first payment number 0 is in A2 and the oustanding balance 90000 for the first payment is in E2

I don't understand where you got a cell called E0? If you multiply 90 000 by the interest rate on a calculator you get 640.95 if you do it again the second interest paid in the interest column will be 3,842.00 i don't understand
we are not talking about the same cells, what do u mean by E0? To get the interest paid we need to multiply the oustanding balance by the interest rate and the outstanding balance is in E2 and this amortization table is not decreasing.

 

[Andrew Mason]

ok in my table that i made
my heading are in the A1, B1, C1, D1 and E1
the first payment number 0 is in A2 and the oustanding balance 90000 for the first payment is in E2

Ok, then use E2 if that is the cell where the 90,000 figure is. In that case, what is your formula for E3? It should be E2-D3.

I don't understand where you got a cell called E0? If you multiply 90 000 by the interest rate on a calculator you get 640.95 if you do it again the second interest paid in the interest column will be 3,842.00

How do you get interest of $3,842 for the second payment? It has to be less than the first interest payment.

If you multiply $90,000 by .08546/12 you get interest of $640.98 which makes the principal payment $86.54 and the balance $89,913.46. Multiply $89,913.46 by .08546/12 you get interest of $640.36 which makes the principal payment $87.16 and the balance $89,826.30.

AM

 

[aisha]

in cell E3 i have =E2+C3-B3 i think that is the same as what you wrote I don't understand why the balance isn't decreasing help me out where is my mistake I followed an example in putting in all the formulas


Hey my teacher told me the payment numbers should be 1-60 not 1-5 because its a table for 5 years with monthly payments.
sooo if we make this adjustment do you get the same value as me for ur outstanding balance due? $83,547.76 this is the last number in the chart cell E62
:rofl:

 

[Andrew Mason]

in cell E3 i have =E2+C3-B3 i think that is the same as what you wrote I don't understand why the balance isn't decreasing help me out where is my mistake I followed an example in putting in all the formulas

The formula is right and if you copy it down your spreadsheet will be right. It looks like you have done that from your answer below.

Hey my teacher told me the payment numbers should be 1-60 not 1-5 because its a table for 5 years with monthly payments.
sooo if we make this adjustment do you get the same value as me for ur outstanding balance due? $83,547.76 this is the last number in the chart cell E62

That is the right answer!

AM