Excel Loan amortization Excel X for Mac 10.1.6

[Larry Rowe]

Please advise on the functions to schedule a fully amortizing loan
outputting periodic (and cumulative) interest and principal payments.

Do I use cumipmt or cumprinc or another function?

Thanks.

 

[Harlan Grove]

Please advise on the functions to schedule a fully amortizing loan
outputting periodic (and cumulative) interest and principal payments.

Do I use cumipmt or cumprinc or another function?

For a fixed rate, fully amortized loan, it's easier to put together an
amortization table using a single PMT function call. Given initial
load amount L, term in months T, and interest rate I expressed as APR,
the monthly payment P would be given by

=PMT(I/12,T,-L)

Then set up your amortization table as follows.

A7: Month
B7: Monthly Principal Payment
C7: Monthly Interest Payment
D7: Monthly Total Payment
E7: Cumulative Principal Payment
F7: Cumulative Interest Payment
G7: Cumulative Total Payment
H7: Principal Balance

A8: 0
H8: =L

A9: =A8+1
B9: =D9-C9
C9: =H8*I/12
D9: =P
E9: =E8+B9
F9: =F8+C9
G9: =G8+D9
H9: =H8-B9

Fill A9:H9 down as far as needed to produce 0 in column H.