Cumulative financial functions
Loan Amortization in Google Sheets
Brent Allen
Instructor
Loan length is not amortization length!
- On short amortization schedules, the loan length and amortization length are normally the same.
- On longer amortization schedules, the loan can be shorter than the total amortization in order to help control risk.
- Amortization length must be the number of periods to reduce the balance on an amortizing loan to zero.
- Loan term can be less than total amortization length to help control risk.
- At the end of the loan, the borrower can enter into a new loan or pay off the unamortized balance in full.
- Loan length must not exceed amortization length!
What are cumulative calculation functions?
Cumulative Calculation Functions provide a balance of all interest and principal payments made on an amortizing loan up to a specified period.
- Allows for quick balance calculations without having to create a full amortization schedule.
- Calculates the balance of interest and principal at a point in time without having to calculate the entire schedule.
- Verifies the accuracy of periodic calculations.
The CUMPRINC() and CUMIPMT() functions.
Principal
Periodic=PPMT(rate, number_of_periods, present_value, end_or_beginning)
Cumulative
=CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
Interest
Periodic=IPMT(rate, number_of_periods, present_value, end_or_beginning)
Cumulative
=CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
Time to accumulate some experience with cumulative functions!
Loan Amortization in Google Sheets
