Mortgage amortization schedule and additional principal

Question

Level 1

5 points

Mortgage amortization schedule and additional principal

Numbers has a ready made template called, 'Mortgage'. When opened it provides a Mortgage Calculator Sheet that needs to be filled in with the loan amount, interest rate and number of years for the loan. When completed, this generates a second sheet called the Data Sheet showing the Monthly Data (Amortization Schedule).
Question: If I were to pay Additional Principal on any given month along with my monthly mortgage payment, where (cell, cells, formula) in the Monthly Data (Amortization Schedule) would I place the this amount to readjust the Schedule?

Mac Pro G5, Mac OS X (10.5.6)

Posted on Jan 4, 2009 9:44 PM

Reply

Answer 1

Jan 5, 2009 9:45 AM in response to chancli73

Interesting question. Making the Mortgage Calculator interactive with regard to payments during the term of the mortgage requires reworking the Monthly Data Table. Here's my offering - hope it does what you want.

Caution: the first row of equations is left intact. For Rows 3 and beyond (Periods 2 and beyond)...

Required Payment: =IF(H2>Mortgage Details :: B$7, Mortgage Details :: B$7, H2*(1+Mortgage Details :: B$5/12))

Principal Payment: =IF(ISBLANK(C3), H2*Mortgage Details :: B$5/12-B3, H2*Mortgage Details :: B$5/12-C3)

Cumulative Principal Payment: =E2+D3

Interest Payment: =-H2*Mortgage Details :: B$5/12

Cumulative Interest Payment: =G2+F3

Principal Balance: =ROUND(H2+D3, 0)

If you have questions, don't hesitate to ask.

Regards,

Jerry

Reply

Answer 2

Mar 18, 2009 7:07 AM in response to Jerrold Green1

Thank you for the info -- I am sorry to say, I am too numbers /excel illiterate to make it work for me! I haven't done anything this extensive with formulas before! I got the first function fine, but when I change the formula in column D3 (Principal Payment) half of it goes to red triangle! I am obviously doing something really wrong. Can you be more specific of steps to change formulas? Thank you for the help!

Reply

Answer 3

Mar 23, 2009 10:07 AM in response to Helms4118

Helms,

Sorry you had trouble with the solution. I may not have made it clear that the first row of equations (in Row 2) is different than the rest of the rows (Row 3 equations and beyond.)

Here are the formulas of Row 2:

B2: =Mortgage Details :: B$7
D2: =IF(ISERROR(PPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4)),0,PPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4))
E2: =D2
F2: =IF(ISERROR(IPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4)),0,IPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4))
G2: =IF(ISERROR(IPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4)),0,IPMT(Mortgage Details:: $B$5/12,A2,Mortgage Details:: $B$6*12,Mortgage Details:: $B$4))
H2: =Mortgage Details:: B4+D2

And these are the formulas for rows 3 and beyond, with relative addressing, of course:

B: =IF(H2>Mortgage Details :: B$7, Mortgage Details :: B$7, H2*(1+Mortgage Details :: B$5/12))
D: =IF(ISBLANK(C3), H2*Mortgage Details :: B$5/12-B3, H2*Mortgage Details :: B$5/12-C3)
E: =E2+D3
F: =-H2*Mortgage Details :: B$5/12
G: =G2+F3
H: =ROUND(H2+D3, 0)

I took these expressions from the still-working document that I saved when I answered the question back in January, running in Numbers '08. I haven't tried the document in Numbers '09, but I guess that it would run there too.

I hope that this additional detail helps.

Jerry

Reply