Download my loan repayment calculator
The basic principles of compounding and discounting
The basic principle of compounding is that if we invest £X now for n years at r% interest per annum, we should obtain £X (1+r)n in n years time. The value, V, attained by a single sum X, after n periods at r% is V = X (1+r)n
If we invest £10,000 now for five years at 10% interest per annum, we will have a total investment worth £10,000 x 1.105 = £16,105.10 at the end of five years.
The basic principle of discounting is that if we wish to have £V in n years' time, we need to invest a certain sum now (time 0) at an interest rate of r% in order to obtain the required sum of money in the future.
If we wish to have £16,105.10 in five years time, how much money would we need to invest now at 10% interest per annum? This is the reverse of the situation described in the second paragraph above.
Let X be the amount of money invested now.
£16,105.10 = P x 1.105
X = £16,105.10 x 1 / 1.105 = £10,000.
If we wish to invest £10,000 now and have £16,105.10 in five years time, what is the required compound interest rate per annum?
The calculation is: (16,105.10 / 10,000.00)(1/5) -1 = 10%
£10,000 now, with the capability of earning a return of 10% per annum, is the equivalent in value of £16,105.10 after five years. We can therefore say that £10,000 is the present value of £16,105.10 at year five, at an interest rate of 10%.
The term present value simply means the amount of money which must be invested now for n years at an interest rate of r%, to earn a given future sum of money.
The formula for discounting
The formula for discounting is:
X = V x 1 / (1+r)n
|Where||V||is the sum to be received after n time periods|
|X||is the present value of that sum (PV)|
|r||is the rate of return, expressed as a proportion|
|n||is the number of time periods (usually years)|
Mortgages and Loans
Lets suppose that we want to buy a house for £230,000, borrowing the entire sum at an interest rate of 7.25% per annum. The mortgage is to be repaid over 25 years.
How do you calculate the monthly repayment?
To calculate the monthly repayments we need to know the following:
The amount of the loan is £230,000 as stated above.
The number of periods over which the loan is to be repaid is 25 years x 12 months = 300 months.
The interest rate is 7.25% per annum - we need to calculate what this is on a monthly basis. We cannot simply take 7.25% and divide it by 12 months because the annual interest rate is compounded on a monthly basis.
Interest is added to the loan each month and then interest is charged on the balance less any capital repayments.
The monthly interest rate is calculated as (1+0.0725)1/12 - 1 = 0.00585 or 0.585%
The formula to calculate the monthly repayments is:
Loan Amount (PV) / Cumulative Discount Factor
The cumulative discount factor is calculated as 1 / r - 1 / [ r(1+r)n ] - so we have 1 / 0.00585 - 1 / [ 0.00585 * (1+0.00585)300 ] = 141.2358
The monthly repayment is therefore £230,000 / 141.2358 = £1,628.48
Lets suppose that we wish to save £45,000 in two years time. Assuming that we can earn 4.35% per annum with interest being paid each quarter, how much do we need to set aside in each of the eight quarters?
The quarterly interest rate is calculated as (1+0.0435)1/4 - 1 = 0.0107 or 1.07%
The £45,000 is the future value of our investment, we need the present value in order to calculate the amount we need to set aside each quarter.
The present value = £45,000 x 1 / 1.01078 = £41,327.
The cumulative discount factor is calculated as 1 / r - 1 / [ r(1+r)n ] - so we have 1 / 0.0107 - 1 / [ 0.0107 * (1+0.0107)8 ] = 7.628
The amount to save is PV / Cumulative Discount Factor: £45,000 / 7.628 =
£5,417.80 per quarter.
Investment Doubling Time
How long does it take for an investment to double at 5% compound interest per annum?
Using the simple Rule of 72. Divide the interest rate into 72. In this case we have 72 / 5 = 14.4 years.
The formula for the exact calculation is: Log(2) / Log(1.05) = 14.2067 years. To work back the other way, use the formula: 1.05^14.2067 = 2. An investment of One Hundred Pounds would be worth: £100 x 1.05^14.2067 = £200.
To work out the time it takes for an investment to triple in value at an interest rate of 5% per annum use the formula: Log(3) / Log(1.05).
To calculate the required rate of interest for an investment to double in a given number of periods (n) use the formula: 2^(1/n) - 1. If we want to find the interest rate necessary for an investment to double in 10 years the calculation is: 2^(1/10) - 1 = 7.18%
If the rate of inflation is 7.18% then money will lose half its purchasing
power in 10 years. An inflation rate of 5% means that the purchasing power
of money will half in every 14.2067 years.
Return to Excel Exchange homepage.