Exploring Repayment Loans
Exploring Repayment Loans
Article Index
Exploring Repayment Loans
Repayment schedules

Chapter Six

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

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.

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

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

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

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

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

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

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

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

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

Repayment loans are the last of the three guises in which annuities appear (see Chapters Four and Five for the other two) and by now you should be growing accustomed to the type of reasoning involved in calculating the effects of regular cashflows.

Repayment Loans

A repayment loan is particularly easy to deal with because it is just an annuity seen from a different point of view.

In the case of a repayment loan a sum of money - the principal or Present Value PV is borrowed at the start of the loan. At the end of each time period a regular sum S is paid back.

This regular sum has to be large enough to pay back the interest due on the loan and some of the principal. As long as this is the case the proportion of interest to principal repaid each period changes so that more and more of the principal is repaid. Eventually the entire principal is repaid and the debt is cleared.

A repayment loan may be just an annuity seen from the other side of the table but the sort of questions you need to answer are subtly different.

Partly this is due to a slight difference in psychology and the nature of the decisions you have to make - you are trying to minimise the interest rate and term rather than maximise them - but it is also the way that institutions and the law deal differently with loans than annuities.

In a repayment loan what interests us most is the size of the repayment given the loan, interest and term but there are still lots of reasons to need to calculate any of the four quantities - repayment, interest rate, term and loan amount - given the remaining three.

Basic math

The basic mathematics of repayment loans is the same as for an annuity.

At the start of the loan the amount PV is borrowed - this is a cashflow in and so positive, however the balance in the account at this point is negative to signify a debt. 

At the end of the first period the debt has increased to:


but the payment of S has also reduced it. That is, at the end of the first period the balance stands at:


at the end of the second period the balance stands at:

[PV*(1+I)-S]*(1+I) - S =

and so on.

In general after n time periods the balance stands at

PV*(1+I)^n - S*(1+I)^(n-1)-
S*(1+I) ...-S

which is of course the same as the situation encountered in the case of the ordinary annuity.

Thus all of the equations and even the spreadsheets that we have constructed for the ordinary annuity apply to the repayment loan.

The only difference is that now the Present Value (PV) is the amount loaned and the payments are to repay the debt. After all one man’s annuity investment is another's repayment loan.

In more practical terms this means that in terms of the cash flows the present value is positive because it is what the borrower receives, i.e. cash in, and the regular payments are negative, i.e. cash out. One final possible confusion is that the financial functions return a negative value of the PV or FV because it represents a debt.



Cash flow in a loan

The function:


will calculate the Future Value FV of a repayment loan of PV being paid back at a rate of S per time period with interest at I%.

As explained before type is zero if the payment is at the start of the period and one if it is at the end.

For a repayment loan the payment is at the start of the period and isn't involved in the interest rate calculation until the following period. Also PV is positive and S (PMT in Excel's terminology) is negative.

In other words if you borrow PV at an interest rate of I% per period and pay the loan back at S per period then FV is the state of the account i.e. the debt after nper periods.

For example, if you borrow $1000 at 15% per annum paid annually then if you repay $200 per year after the first year the account stands at:


or -$950 i.e. the debt has reduced by $150. After 1 year the debt has increased to -$1150 and you have repaid $200 making a total of -$950 as given. In 10 years the debt is reduced to


which works out to $15.19 and a positive value indicates that the loan has been over paid by this amount - i.e. the loan was fully repaid sometime during the 10th year.

Number of periods to repay the loan

The FV function can be used to give the balance after any number of periods but it is also important to know when the loan is repaid - i.e. when the FV becomes zero.

To calculate the number of periods needed to reduce the Future Value to zero you can use the NPER function that has already been introduced in earlier chapters but this time with the FV set to zero:


For example, how long will it take to repay a loan of $1000 at 15% at a rate of $200 per year? The answer is:


which works out to approximately 9.92 years. This agrees with the earlier calculation that the same loan is repaid before 10 years are up.

Present Value

In the case of a repayment loan the present value represents the amount that can be borrowed if you can find a loan at a given rate, term and payment.I

Most spreadsheets provide a financial function to work this out. The PV function has been introduced in earlier chapters but in this case FV is generally set to zero:


gives the amount you can borrow if the interest rate is I and the loan is repaid with n periodic payments of S.

For example, if you can pay $200 per month for 10 years at 15% per annum then you can borrow:


which works out to $1003.75 - a little more than the $1000 in the previous examples.

Payments needed

Another important repayment calculation is the amount needs to be paid to clear a loan at a given rate and term. As repayment loans are generally arrange on the basis of the amount for a given term this is particularly important.

This is achieved using the PMT function again with FV set to 0::


where I is the interest rate, n the number of periods, PV the size of the loan.

For example, how much do you have to pay to repay a loan of $1000 at 15% in exactly 10 years? The formula needed is:


which works out to -$199.25, which again should be compared to the result given earlier.

The interest rate needed

A far less common calculation for a repayment loan is to find the interest rate needed to repay the loan in a given time at a given rate.

The reason is simply that usually you are in search of the lowest rate available not the one that fits the time period and repayments exactly. However the calculation is worth exploring for completeness and just in case it is needed as a way of judging the value of a given financial arrangement in terms of the equivalent repayment loan rate.

Once again, as in the case of the savings plan,  solving for I turns out to be impossible and iteration has to be used. In most cases you can simply use the RATE function with FV set to zero:

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

where n is the number of periods, S the payment, PV the loan, type is zero or one depending on whether the payments are the start or the end of the time periods and guess is a guess at what the interest rate is. If you don't specify a guess then a random value is used as the starting value.

For example,what rate do you need to completely repay a loan of $1000 over 10 years of repaying $100 per year? The formula you need is:


which works out to 15% per annum, a value that again should be compared to the previous examples.










RSS feed of all content
I Programmer - full contents
Copyright © 2015 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.