You could also use Excel to create a traditional amortisation table to calculate what portion of any repayment is capital or interest.
To follow along go to 17.3
As shown below we can use a bucket to create the table and then create a new row which calculates the capital portion of the repayment (total repayment minus interest repayment will give the capital portion).
This is a manual method but there are some functions that do it i.e.
PPMT (Principal portion of the payment) and IPMT (Interest portion of the payment)
The only extra requirement with PPMT is that you have to specify the Per (period you want to know the principal/ capital portion).
As shown below, we have used a header row so that we can copy the formula across. In the chosen cell (month 5) it shows what the capital portion would be in a normal amortization in the 5th period of a 12 month loan.
IPMT works exactly the same way.
It is important to note that you cannot ask for either the PPMT or the IPMT for a period that is bigger than the life of the loan i.e. you can’t ask for the capital portion of month 13 if it is only a 12 month loan.
Also, PPMT and IPMT only calculate one period at a time. So it is not possible to ask for the total of the first 6 months principal payments in a single calculation.
Calculate the Cumulative capital or interest between 2 periods (CUMPRINC, CUMIPMT)
An older function exists to help with this calculation but you need to be careful as it has two shortfalls.
As shown below the requirements are the same as the other time value of money functions but you now need to specify the Start Period and the End Period. So if I want to see how much capital is repaid in the first 6 months of the loan I would specify
- Start_Period= 1
- End_Period= 6.
CUMIPMT works exactly the same.
The things to watch out for with these functions are:
- they don’t allow for a different FV (future value) so all loans are assumed to amortise to zero (fully repaid)
- you have to enter the Type option (beginning or end of period) otherwise it doesn’t work. You may miss it though as you have to scroll down a bit.