Benches Uncategorized

3

Mortgage amortization schedule and additi…

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

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?

3 replies

Sign in or join Beachball Help to reply.

  1. 0

    Jerrold Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    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.

    User uploaded file

    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

  2. 0

    Helms4118

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    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!

  3. 0

    Jerrold Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    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