Now that we have covered the basic functions that relate to time value of money, let’s look at how we can enhance our debt calculations to allow for real world scenarios.
The first one is how to allow for ad hoc withdrawals and pre payments so that the debt recalculates to meet the original life of loan. So it is a 12 month loan and we should pay 5 731 per month but we need to take out another 50 000 in month 6. What needs to happen to the repayments so that at the end of the 12 months we are at the anticipated future value?
To follow along go to 17.3
In order to understand how to do this, we first need to prove that mathematically we can re calculate the repayment required each month as if the loan only has the remaining life left. So in month 1 of a 12 month loan we calculate based on a loan of 12 months but when we are in month 2 we assume that we are only dealing with a 11 month loan and so on. All things being equal will it give us the same numbers?
Below note a few things about the revised PMT calculation:
- The header we use (red box) is going backwards from the life of the loan to 1. This is so that in each month we know how many months are left in the loan.
- In the PMT function the NPER (number of periods) is NOT the static 12 but rather how many months are left per the header.
- The PV (present value) is that month’s opening balance.
When you copy this across each month it is almost stand alone. It is as if there are 12 separate loans each with a different loan period. The only link is the opening balance. Note that the repayment numbers are exactly the same as our static calculations. So mathematically, all things being equal, you can re calculate a loan each month as if it is a new loan but get the same result as if you have entered a static loan.
Why is this useful?
Making additional drawdowns or prepayments on loans
The benefit of this method is that you can now put money into the loan or take it out at any point in time and the calculations will make sure that by adjusting the repayments you end up with the same residual value when the loan ends.
Notice below that in K36 I have added 100 000 to the opening balance (additional drawdown- not the best practice way- you should rather have a new row!) and in M36 I have subtracted 50 000 (prepayment). The repayments adjust themselves so that at the end of the loan in month 12, we are left with the residual we expect.
The same logic applies with the PPMT and IPMT functions. There is only one small catch.
When you are asked for the PER (period of the calculation) you need to hard code a 1. This is because each month is treated as a standalone loan, so in the example below we are in month 1 of a 7 month loan. As it is copied across the life of the loan will decrease but in each case you are always in the 1st month for Excel’s purposes.