Repayment loans are often arranged and then subject to much on-going scrutiny and possible rearrangement in the light of new financial circumstances.

As a result the actual detailed workings of the change in the debt and where the payments are going are usually of concern. The same calculations apply to both annuities and savings plans with minor modifications but generally few bother.

As well as knowing the values relating to the end of the loan or annuity it is also often necessary to know the balance at any time.This is just the future value at the specified time.

That is, the balance of a loan of PV at I% repaid at S per period after n periods is:

=FV(I,n,S,PV)

For example, a loan of $1000 at 15% repaid at $200 per annum has a balance after 6 years of:

=FV(15%,6,-200,1000)

which is -$562.31.

As well as knowing the balance you might also want to know the amount of the principal that has been repaid . This is simply the difference between the PV and the balance, i.e. the reduction in the debt:

=PV-FV(I,n,S,PV)

For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount of the loan repaid is:

=-1000-FV(15%,6,-200,1000)

which works out to -$437.69.

In the same way the amount of interest paid to date is easily found as the difference between the total amount paid and the amount of the loan repaid :

=n*S-PV+FV(I,n,S,PV)

For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount paid in interest is:

=-6*200+1000+FV(15%,6,-200,1000)

which works out to -$762.31.

There are a range of spreadsheet functions that will give you the amount of the loan and the interest repaid in any given time period - but it is generally easier to use the basic functions you have.

All of the functions given above work out a cumulative total - e.g. cumulative total repaid and cumulative total interest. You can use these to find the amount in any given period, n say, simply by taking the difference between the cumulative amount at time n-1 and at n.

For example: the amount of the debt repaid in time period n is just the difference between FV at period n-1 and period n:

=FV(I,n-1,S,PV)-FV(I,n,S,PV)

and the amount paid in interest in period n is just the difference in cumulative interest between period n-1 and n:

=(n-1)*S-PV+FV(I,n-1,S,PV)- n*S+PV+FV(I,n,S,PV)

which after a little algebra reduces to:

=FV(I,n-1,S,PV)-FV(I,n,S,PV)-S

You should see that this is just the difference between the payment and the amount paid off the debt in that period. This is exactly what you would expect as each payment goes in part to pay off the loan amount and part to pay the interest.

To see these formulas in action see the example on the next page and the Janet Swift's loans and savings spreadsheets.

Formula summary

For a repayment loan type is set to zero or omitted and FV is zero:

FV the balance

=FV(I,n,S,PV,type)

S the payment

=PMT(I,n,PV,0,type)

n the term

=NPER(I,S,PV,0,type)

I the rate

=RATE(n,S,PV,0,type,guess)

Total repaid at n

=PV-FV(I,n,S,PV)

Total interest at n

=n*S-PV+FV(I,n,S,PV)

A schedule calculator

Using these formulae it is quite easy to put together a spreadsheet that will detail the state of a repayment loan at any stage in its life. The only real problem is allowing for the required number of months in the table.

First enter all of the text and Loan, Rate and Term data as shown in below.

The basic loan spreadsheet

Enter 1 in A8 to start the formulae off and enter:

=FV($B$2/12,A8*12,$B$5, $B$1) in B8 =$B$5*A8*12 in C8 =$B$1+B8 in D8 =-(C8-D8) in E8

The first formula computes the state of the account at the end of each year. The second works out the total amount paid by the end of the year and the final two work out the amount of the debt paid off and the amount paid in interest to date.

Next fill column A with as many year numbers as you are interested in and copy the formulae in B8:G8 into the same number of rows.

You can see an example of a 25-year loan in Figure 1. Notice the way that the first year's payments only succeeds in paying off $21.02 of the principal although over $4400 is paid! Over the 25-year period of the loan more than $130,000 is paid in interest.

If you look a graph of the balance against time you cansee the characteristic shape of the repayment loan. The principal is paid off only slowly at first but the speed of repayment increases dramatically in the final years.

Notice that there is no real decrease in the amount owed for the first 15 years of the loan. This is the reason why additional payments early in the life of a loan can greatly reduce the total cost.

The rate of decrease of a repayment loan

Financial Functions

Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.

Understanding Percentages Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.

Interest Simple and Compound We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment. The ideas of Present and Future Value PV and FV are introduced.

Effective Interest Rates We explore the idea of the `effective’ annual interest rate and then on to the Effective Interest Rate/Annual Percentage Rate, the much quoted EIR or APR.

Introduction to Cashflow - Savings Plans In the first of three chapters covering the way in which interest rate affects cashflow we explore savings - but first we introduce some general ideas that apply equally to annuities and repayment loans.

Cashflow Continued - Annuities We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects

Exploring Repayment Loans Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.

Present and Future Values The principles of present and future value apply even if the cash flow is irregular. The calculations are just a matter of breaking down the cash flow calculations into simple steps.

Investment analysis How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.

IRR The Internal Rate of Return The IRR is perhaps the most complicated of the measures of the value of an investment with an irregular cash flow. Understanding exactly what it means is a good step toward making correct use of it.