NPER page
Introduction to Capital Investment Appraisal
Compound Interest
Finding the interest rate value and applying it in a budgeting setting
In August 2000, Richard Lee sent in a problem to the Excel-G discussion group and here is my response to that problem.
What follows is an apparently lengthy discussion but you will see with practice
that it boils down to some relatively straightforward mathematics in the end.
This example is concerned with target future values and having to find the
value of r in order to carry out compound interest calculations, where r is the
rate of change or the rate of interest. The problem also involves the concept
of month 0.
The basic problem boils down to this
Solution and discussion
Using compound interest theory, we know that
We also know that we are dealing with only
11 months in this case
So, to determine the monthly rate of interest to apply, we need to rearrange the
function to give what we are looking for: we need to find the
11th root of 1.2
How do we find the 11th root of this, or anything for that matter?
We can
find the 11th root by raising the amount to the
power of the reciprocal of the root value
: that is, 1/11 in this case, giving
Excel application of the solution
That was a long winded road to travel down. Let's apply the solution now by
drafting a table that starts with Richard's opening budget allocation of $100
and ending with his closing budget allocation of $120
Two questions remain to be answered, perhaps:
January is month 0 in this example because
it is the starting point and it is given to us without any need to increase it
by the compound factor. More than this, given that the budget allocation is
probably made at the
BEGINNING
of the month, there can be no need to increase it. It is also true that the
February allocation will be made at the beginning of February, not the end,
which is technically equivalent to being at the end of January. This means that
we only need to call February month number 1, not number 2.
So, in the compound interest function, the value found in cell A55 is 0 not 1
and the vlue found in cell A56 is 1 not 2 ...
A total of 12 periods, with the values extending from 0 - 11 not 1 - 12. Note:
if you use 1 - 12, you will get different answers to the ones in the table.
In compound interest and capital budgeting spheres, month 0 is the same as year
0, which we always take to be the very beginning of a project or case, or
TODAY. At the beginning of a project, we use the month or year 0 concept for
mathematical reasons, as shown above, and to reflect that fact that the PRESENT
VALUE of $1 due to be paid or received today is $1. If you apply the compound
interest function to this idea, you will see that m = 0 here.
Budgeting beyond December
The month 0 concept effectively disappears when move into year two of
a project lasting more than two years in that it only affects the very
beginning of the budget.
Keeping the example as simple as possible, let's imagine that we want to budget
a further 12 months for Richard's organisation. We use exactly the same rate of
change and we will continue with the same allocations for the year 2000 and
model the year 2001 as a straight continuation. The table that follows shows
all of this:
We could also link the value of the Principal ($100) to one cell in the
spreadsheet as we have done with the compound interest factor in cell B116; the
Excel Function would then be, for example, =$B$117*$B$116^F103 for January
2001; with the principal amount being kept in cell B117.
Additional questions to think about
Conclusion
Compound interest calculations are rarely as straightforward as we would like.
Even when we have highly sophisticated spreadsheet software, we still find the
need to ask the kind of question that this page has been designed to answer.
Nevertheless, with a logical approach and a clear mind, working through several
examples like this one will help you to become expert enough in this area.
Let me give you some
words of encouragement, though: a few years ago, I found that my Bank had miscalculated my annual
interest entitlement, so I complained. To cut a long story short, after several
MONTHS of exchanging letters and failing to convince them they should check my
account properly, I sent the bank manager a letter with the kind of formulae
that this page contains, showing them that I was right. They then gave in
immediately: all because I'd learned compound interest! It could save you a
fortune like it did for me.
Duncan Williamson
If I want a budget allocation to grow from $100 as at the beginning of January
to $120 as at the beginning of December, what will be the monthly allocation
assuming an average rate of growth of the monthly budget and assuming interest
is compounded on a monthly basis?
The monthly amount will be P*(1+(r/m))
m
where
We know the value of P: $100
We know that the desired value of (1+(r/11))
11
is 1.2: that is $120/$100
Consequently, to find the value of r, the workings are as follows:
The monthly amount will be P*(1+(r/11))
m
the compound interest factor after 11 months is (1+(r/11))
11
, which, at the end of the year, we know must be 1.2
1.2
(1/11)
= 1.0167128 = (1+(r/11))
11
Proof
: (1.0167128)
11
= 1.2
So the monthly rate of increase, or interest, we are looking for is 0.0167128
or 1.67128%
Note
: make sure you can appreciate which is cell A2, A3 ... A13 as shown in the
Excel Function column. We could have simplified the function, but for demonstration purposes I have
left it alone. I'll correct for this in the next table.
Month
Start of
Budget ($)
Excel Function
0
Jan
100.00
=100*(1.2^(1/11))^A2
1
Feb
101.67
=100*(1.2^(1/11))^A3
2
Mar
103.37
=100*(1.2^(1/11))^A4
3
Apr
105.10
=100*(1.2^(1/11))^A5
4
May
106.85
=100*(1.2^(1/11))^A6
5
Jun
108.64
=100*(1.2^(1/11))^A7
6
Jul
110.46
=100*(1.2^(1/11))^A8
7
Aug
112.30
=100*(1.2^(1/11))^A9
8
Sept
114.18
=100*(1.2^(1/11))^A10
9
Oct
116.09
=100*(1.2^(1/11))^A11
10
Nov
118.03
=100*(1.2^(1/11))^A12
11
Dec
120.00
=100*(1.2^(1/11))^A13
Month 0
We apply this philosophy all of the way down the list to December, which is
classified as month 11.
Year 2000
Year 2001
Month No
Start of
Budget ($)
Function
Month No
Start of
Budget ($)
Function
0
Jan
100.00
=100*$B$116^A102
12
Jan
122.01
=100*$B$116^F103
1
Feb
101.67
=100*$B$116^A103
13
Feb
124.04
=100*$B$116^F104
2
Mar
103.37
=100*$B$116^A104
14
Mar
126.12
=100*$B$116^F105
3
Apr
105.10
15
Apr
128.23
4
May
106.85
16
May
130.37
5
Jun
108.64
17
Jun
132.55
6
Jul
110.46
18
Jul
134.76
7
Aug
112.30
19
Aug
137.01
8
Sep
114.18
20
Sep
139.30
9
Oct
116.09
21
Oct
141.63
10
Nov
118.03
22
Nov
144.00
11
Dec
120.00
=100*$B$116^A113
23
Dec
146.41
=100*$B$116^F114
(1+(r/11)) =
1.016712809
13 October 2001
© Webmaster Duncan Williamson 2001