Partial Loan Amortization Schedule
Now we consider a case where we are interested in the details (Interest portion, Principal portion, or Principal Balance) of a particular payment in the amortization schedule.
Sometimes it is not efficient to construct a full amortization schedule just to read details about a specific payment or a specific set of payments within the amortization table.
Example – Let’s look at the example from the previous page.
Instead of re-constructing her amortization schedule from payment 0, Grace would like to determine the details of her 4th payment. Since she is only interested in the 4th payment, she does not want to have to construct the entire table. Calculate the Balance, Principal Portion of the payment, and Interest Portion of the payment for the fourth payment
To determine this information, we can start with the spreadsheet that we used to calculate her payments. To determine the information Grace wants, we are are going to use the following Excel functions:
The function will give the cumulative interest paid on a loan between start_period and end_period.
The function will give the cumulative principal paid on a loan between start_period and end_period.
where:
- Rate is the interest rate.
- Nper is the total number of payment periods.
- Pv is the present value.
- Start_period is the first period in the calculation. Payment periods are numbered beginning with 1.
- End_period is the last period in the calculation.
- Type is the timing of the payment. (0 – end, 1 – beginning)
We start with the same information that we used to calculate the her payments. Once we know the payment need, we can use the two functions, CUMIPMT and CUMPRINC to determine the principal and interest portions of the payment.
Note: to find the outstanding balance, there are two different ways we can do this:
- set the first payment in the CUMPRINC function to 1 to determine how much principal has been paid off from the beginning. We can subtract that from the initial loan to determine the balance owing.
- use the FV function to find the FV of the loan at the time of the given payment.
To see the complete solution, see the following spreadsheet: Partial loan amortization – Template
To Learn more about the CUMIPMT and CUMPRINC functions, see the support pages on the Microsoft Support page:
NOTE
The following two functions could also be used to calculate the Interest Portion and Principal Portion of a payment, but they only allow you to look at one payment. The CUMIPMT and CUMPRINC functions are more flexible and allow you to look at one payment or the sum of a range of payments.
- IPMT(rate, per, nper, pv, [fv], [type])
- PPMT(rate, per, nper, pv, [fv], [type])
