# Mortgage monthly payment?

Tags:
1. Apr 7, 2005

### 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?

7. Apr 8, 2005

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 ©) 8. Apr 8, 2005 ### shmoe Why didn't you use your 8.546% interest as the monthly rate? They said the interest was calculated monthly. 9. Apr 8, 2005 ### Andrew Mason 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

Last edited: Apr 8, 2005
10. Apr 11, 2005

### 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 dont 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 cant be right both of us didnt use the nominal interest rate if we didnt have to use it then why did we find it? 11. Apr 11, 2005 ### Andrew Mason 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 12. Apr 12, 2005 ### 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 Last edited: Apr 12, 2005 13. Apr 12, 2005 ### Andrew Mason 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 14. Apr 12, 2005 ### aisha sorry I edited my post i meant present not future. I dont if im 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 Last edited: Apr 12, 2005 15. Apr 12, 2005 ### Andrew Mason 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 16. Apr 12, 2005 ### 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 dont 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 dont 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. 17. Apr 12, 2005 ### Andrew Mason 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. 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

18. Apr 13, 2005

### aisha

in cell E3 i have =E2+C3-B3 i think that is the same as what you wrote I dont 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:

Last edited: Apr 13, 2005
19. Apr 13, 2005

### Andrew Mason

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.