A Debt to Repay
In this article we consider how to model debt repayment calculations from a practical perspective. By Liam Bastick, Director with SumProduct Pty Ltd.
Query
Any advice on how I can model my credit card debt? It seems like a topical question in the current economic climate!
Advice
It’s often in turbulent times that our thoughts turn to loved ones, health and credit card debts. I cannot help you much with the first two, but this month’s article addresses three common calculations using Excel’s financial functions sparingly.
For completeness, my examples include the seemingly more convoluted mathematical formulae which arrive at the same answer. This is because some of the useful functions (e.g. CUMPRINC) are not in the “basic” Excel function directory and will not be recognised without the Analysis ToolPak add-in installed in some versions of Excel (use ALT + T + I to check whether this add-in is installed). The mathematical alternatives work without relying upon this add-in using no more than the standard operators plus the LOG function on occasion.
For simplicity, the discussion below will focus on scenarios where payments are made at the end of each period (i.e. payments are “in arrears”). All of the following examples are considered further (including when payments are made “in advance”) in the attached Excel file.
Problem 1: Minimum Payment Calculations (PAiN Relief)
This is often referred to as the mortgage calculator. Here, the aim is to calculate what the regular repayment is per period to service and pay off a debt over a given amount of time.
For example, if I borrow $300,000 over 25 years at an interest rate of 12% p.a. what will my regular monthly payments be (assuming no change of rate)?
The answer to this question is given by the formula:
P = Ai / (1 – (1 + i)-N),
where;
- P = regular periodic payment;
- A = amount borrowed;
- i = periodic interest rate; and
- N = total number of repayment periods.
(Interesting that the acronym for remembering the mortgage variables is PAiN!)
In our example, crunching the numbers (using a periodic interest rate of 1%, being 12% / 12 and total number of periods being 25 x 12 = 300) gives a monthly repayment of $3,159.67.
The same calculation could have been performed in seconds using Excel’s built-in PMT function:
PMT(rate,number_of_periods,amount_of_loan),
It should be noted that using PMT will give the same solution, but be negative instead. This is because Excel’s financial functions distinguish between cash inflows (positive) and outflows (negative).
Problem 2: Calculating the Outstanding Balance
If we are looking to re-finance or pay off a loan, we need to be able to forecast the balance outstanding at a point of time (ignoring penalties, etc.).
Continuing the example from above, I have borrowed $300,000 over 25 years at an interest rate of 12% p.a. and I am making my monthly (minimum) repayments. After three years, I am able to re-finance my loan without incurring any penalties. What would be the outstanding amount at this point in time?
The answer to this question is given by the formula:
B = A(1 + i)n – (P((1 + i)n – 1) / i),
where;
- B = balance outstanding;
- P = regular periodic payment;
- A = amount borrowed;
- i = periodic interest rate; and
- n = number of periods payments have been made (n <= N).
Using the same periodic interest rate of 1%, the monthly repayment of $3,159.67 (calculated above) and using 36 (12 x 3) periods for n, the amount outstanding would be $293,121.81, i.e. only $6,878.19 (principal) has been paid off. More scarily, if we calculate the total of the payments made (36 x $3,159.67), then the inferred interest is a whopping $106,870.02! Best not to think too long about it…
The same calculation could have been performed quickly using Excel’s CUMPRINC function:
CUMPRINC(rate,life_of_loan,amount_of_loan,start_period,end_period,type),
This requires explanatory notes:
- Again, the rate is the periodic rate (here 1% not 12%);
- life_of_loan is the total length of the loan, not just to date. This is needed to calculate implicitly the regular repayment amount;
- CUMPRINC actually calculates the amount of principal paid off for an interval of time (CUMPRINC = cumulative principal). Therefore, the start and end dates must be stipulated, and if we are calculating the aggregate from the beginning, the start_date must be 1 (first period);
- The type determines whether payments are made in arrears (type 0) or in advance (type 1); and
- To calculate the balance outstanding using this method we simply compute Amount + CUMPRINC calculation.
It should be noted that, similar to PMT, CUMPRINC gives a negative value for similar reasons. This is why the formula employs addition rather than subtraction.
Problem 3: How Long Before the Debt is Paid Off?
What if we decide to turn the problem around? Instead of deciding how long we want to take to pay off the debt, how do we calculate how long a debt will take to pay off if we decide to stipulate what the regular payment will be each month instead?
Returning to my example, if I borrow $300,000 at an interest rate of 12% p.a. how long will it take to pay off if I pay $5,000 each month? This does assume that our payment exceeds the accrued interest each month (here, $300,000 x 1% = $3,000).
The answer to this question is given by the formula:
N = -log(1 – (Ai / P)) / log (1 + i),
where:
- N = total number of repayment periods;
- A = amount borrowed;
- i = periodic interest rate; and
- P = regular periodic payment.
(The logarithmic base is irrelevant as long as the same one is used for both the numerator and the denominator of the above quotient.)
Plugging the inputs into the formula gives us a value of 92.09, i.e. 93 periods (fractions of periods do not really make sense here).
The same calculation could have been computed simply using the NPER function:
NPER(rate,-payment,amount_of_loan)
Note that the payment must be negative and the amount of the loan must be positive.
Extensions
There are several other functions which can answer questions posed modifying our scenarios above. Common functions include PPMT, IPMT and RATE (amount of principal paid in a given period of time, amount of interest paid in a given period of time [note that PPMT + IPMT = PMT for any one period] and the implied interest rate for an annuity, respectively). However, with the exception of RATE (where goal seek is often used instead), these other functions can often be circumvented using extensions of ideas illustrated in the examples above.
As with many things in Excel, there is more than one approach to the correct answer.
Related Articles
Like this article? Here are some other articles you may find interesting.
- Interest Received- How to calculate interest received on the average cash balance for the period. MODELLING
- Irreverent IRR - Understanding some of the issues associated with the Internal Rate of Return (IRR) in Excel. FUNCTIONS
- Modified Internal Rate of Return Revisited - Considering how to calculate "XMIRR": evaluating the return for aperiodic intervals. FUNCTIONS
