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:

(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).

 

Example 1: Repayment Calculation

 

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:

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:

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.

 

Example 2: Balance Outstanding

 

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:

(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.

 

Example 3: Payment Duration

 

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.