Documentation/How Tos/Calc: Derivation of Financial Formulas
Contents
Derivation of Financial Formulas
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.
Most users will of course not wish to investigate further, and can ignore this page.
!! This page is currently in development !!
DB
This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.
We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.
In the first year
Starting book value = c
Depreciation = cr
Remaining book value = c(1 - r)
In the second year
Starting book value = c(1 - r)
Depreciation = c(1 - r)r
Remaining book value = c(1 - r)(1 - r)
=c(1 - r)2
In the tth year
Final remaining book value = c(1 - r)t
So
s = c(1 - r)t
(s/c)1/t = (1 - r)
r = 1 - (s/c)1/t
The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.
DDB
As with DB, the book value at the end of life is:
c(1 - r)t
or the salvage value s if that is greater, where by definition: r = f/t (factor f and lifetime t).
The circumstances in which DDB will not depreciate to the salvage value are therefore:
s < c(1 - r)t
=>
s / c < (1 - f/t)t
SYD
With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:
Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.
The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).
The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).
The depreciation in year y is therefore
(c - s) * (t+1 - y) / [ t(t+1)/2 ]
= (c - s) * (t+1 - y) * 2/ [t(t+1)]
Time Value of Money
Present and future values
We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.
The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.
The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.
We will calculate a future value f of a sum p that we have today. Let us say there is an alternative investment with a rate r which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.
At first our investment is worth p.
At the end of the 1st year it is worth p plus a year's interest
= p + pr = p(1 + r).
At the end of the 2nd year it is worth p(1 + r) plus a year's interest
= p(1 + r) + p(1 + r)r = p(1 + r)2.
...
At the end of the nth year it is worth p(1 + r)n.
So the future value after n years is:
f = p(1 + r)n.
The present value is therefore:
p = f / (1 + r)n.
We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.
Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.
Also note that this model assumes that the rate stays the same.
EFFECTIVE; EFFECT_ADD; (EFFECT)
An investment is currently worth p, and pays interest at a rate of r each period, compounded.
From above, at the end of the nth period, the value of the investment = p(1 + r)n.
If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.
As the interest compounds each period, the value of the investment at the end of the (say) year will be:
p(1 + r)n = p(1 + x/n)n.
We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.
p(1 + e) = p(1 + x/n)n
therefore
e = (1 + x/n)n - 1
PMT, IMPT, PPMT
NB - this derivation will shortly be replaced with a simpler one, in terms of future values.
We take out a loan. We will calculate an unchanging periodic payment which will pay (fixed rate) interest charges, and will steadily reduce the sum borrowed until there is a certain sum still owing after n periods.
m = payment each period (does not change)
n = number of periods (a period may be a month, a year or whatever)
p = principal (the capital sum borrowed)
f = the final balance owing after n periods
r = interest rate (fixed rate) per period (not necessarily per month or year)
If the payments are made at the end of each period:
At the end of the first period:
interest payable is pr
capital repaid = m - pr
therefore capital still owed = p - (m - pr) = p(1+r) - m
At the end of the second period:
interest payable = (p(1+r) - m )r
capital repaid = m - (p(1+r) - m )r
therefore capital still owed = [p(1+r) - m] - [m - (p(1+r) - m )r]
= p(1+r) - m - m + p(1+r)r - mr
= p(1+r)(1+r) - m - m(1+r)
= p(1+r)2 - m - m(1+r)
= p(1+r)2 - m(1 + (1+r))
At the end of the third period:
interest payable = (p(1+r)2 - m - m(1+r))r
capital repaid = m - (p(1+r)2 - m - m(1+r) )r
therefore capital still owed = [p(1+r)2 - m - m(1+r)] - [m - (p(1+r)2 - m - m(1+r))r]
= p(1+r)2 - m - m(1+r) - m + (p(1+r)2 - m - m(1+r))r
= p(1+r)2 - m - m(1+r) - m + p(1+r)2r - mr - m(1+r)r
= p(1+r)2 + p(1+r)2r - m - m(1+r) - m(1+r) - m(1+r)r
= p(1+r)3 - m - m(1+r) - m(1+r)2
= p(1+r)3 - m(1 + (1+r) + (1+r)2)
Now
1 + (1+r) + (1+r)2
looks like a geometric series 1+ a + a2 .... ax-1
which = (1+ax)/(1-a) (a standard result for a geometric sequence)
Here:
1 + (1+r) + (1+r)2 ..... (1+r)x-1 = (1-(1+r)x)/(1-(1+r)) = (1-(1+r)x)/(-r) = ((1+r)x-1)/r
This is true for x = 3.
For any x, if the capital owed at end of xth period = p(1+r)x - m((1+r)x-1)/r
then at the end of the (x+1)th period:
interest payable = (p(1+r)x - m((1+r)x-1)/r)r
capital repaid = m - (p(1+r)x - m((1+r)x-1)/r)r
therefore capital still owed
= p(1+r)x - m((1+r)x-1)/r - [m - (p(1+r)x - m((1+r)x-1)/r)r]
= p(1+r)x + p(1+r)xr - m((1+r)x-1)/r + m((1+r)x-1)/r)r - m
= p(1+r)x(1+r) - (m((1+r)x-1)/r)(1+r) - m
= p(1+r)x+1 - m((1+r)x+1-(1+r))/r - m
= p(1+r)x+1 - m((1+r)x+1-1)/r + mr/r - m
= p(1+r)x+1 - m((1+r)x+1-1)/r
Thus if the formula is true for any x, it is true for x+1. The result is true for x = 3, and therefore true for all x. Therefore:
At the end of the nth period:
capital still owed = f
= p(1+r)n - m(1 + (1+r) + (1+r)2 ..... (1+r)n-1)
= p(1+r)n - m((1+r)n-1)/r
Therefore the formula for PMT is:
m = [p(1+r)n - f]r / ((1+r)n-1)
[Note that this formula is unstable in calculation for tiny values of r (almost dividing by zero), so needs to be implemented in software carefully.]
For IPMT:
We know the capital at the end of the xth period is:
p(1+r)x - m((1+r)x-1)/r
so at the start of the xth period it is:
p(1+r)x-1 - m((1+r)x-1-1)/r
The interest for that xth period is:
[p(1+r)x-1 - m((1+r)x-1-1)/r] r =m - (m-pr)(1+r)x-1
For PPMT:
The capital paid off is m - period_interest
=m - [m - (m-pr)(1+r)x-1]
=(m-pr)(1+r)x-1
If payments are made at the start of each period:
(including at the start of the first period)
At the start of the first period:
interest payable is 0
capital repaid = m
therefore capital still owed = p - m
At the start of the second period:
interest payable is (p-m)r
capital repaid = m - (p-m)r
therefore capital still owed = p - m - (m - (p-m)r)
= p - m - m + (p-m)r = p - m - m + pr - mr
= p(1+r) - m - m(1+r)
At the start of the third period:
interest payable is (p(1+r) - m - m(1+r))r
capital repaid = m - (p(1+r) - m - m(1+r))r
therefore capital still owed = p(1+r) - m - m(1+r) - (m - (p(1+r) - m - m(1+r))r)
= p(1+r) - m - m(1+r) - m + (p(1+r) - m - m(1+r))r
= p(1+r) - m - m(1+r) - m + p(1+r)r - mr - m<//i>(1+<i>r)r
= p(1+r) + p(1+r)r - m - m - mr - m(1+r) - m(1+r)r
= p(1+r)2 - m - m(1+r) - m(1+r) 2
At the start of the nth period:
capital still owed = p(1+r)n-1 - m(1 + (1+r) + (1+r)2..... (1+r)n-1)
= p(1+r)n-1 - m((1+r)n-1)/r
At the end of the nth period:
interest payable/owed is (p(1+r)n-1 - m((1+r)n-1)/r)r
There is no payment
capital still owed = f
= p(1+r)n-1 - m((1+r)n-1)/r + (p(1+r)n-1 - m((1+r)n-1)/r)r
= p(1+r)n-1 - m((1+r)n-1)/r + p(1+r)n-1r - m((1+r)n-1)
= p(1+r)n-1 + p(1+r)n-1r - m((1+r)n-1)/r - m((1+r)n-1)
= p(1+r)n - m((1+r)n-1) (1 + 1/r)
= p(1+r)n - m((1+r)n-1) (1 + r)/r
= p(1+r)n - m((1+r)n+1-(1+r)) /r
= p(1+r)n - m(1 + r)((1+r)n-1)/r
=>
m = [p(1+r)n - f]r/[(1+r)((1+r)n-1)]
PV,FV
PV calculates present value using essentially the same model as PMT above:
m = constant payment each period
n = number of periods (a period may be a month, a year or whatever)
p = present value
f = future value - ie the final sum paid to you
r = interest rate (unchanging) per period (not necessarily per month or year)
The equations are therefore the same:
If the payments are made at the end of each period:
f = p(1+r)n - m((1+r)n-1)/r
=>
p = (f + m((1+r)n-1)/r) / (1+r)n
If payments are made at the start of each period:
(including at the start of the first period)
f = p(1+r)n - m(1 + r)((1+r)n-1)/r
=>
p = (f + m(1 + r)((1+r)n-1)/r) / (1+r)n
ISPMT
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.
n = number of periods (a period may be a month, a year or whatever)
p = principal (the capital sum borrowed)
r = interest rate (fixed rate) per period (not necessarily per month or year)
Repayment of capital each period (does not change) = p/n
At start of 1st period:
Capital outstanding = p - p/n = p(n-1)/n
Interest for the period = (p(n-1)/n) r
At start of 2nd period:
Capital outstanding = p(n-1)/n - p/n = p(n-2)/n
Interest for the period = (p(n-2)/n) r
At the start of the xth period:
Capital outstanding = p(n-x)/n
Interest for the period = (p(n-x)/n) r
=pr(n-x)/n
YIELDMAT
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:
maturity _value / settlement_value = 1 + yield*YEARFRAC(settlement;maturity),
where YEARFRAC(settlement;maturity) is the fraction: number_of_days_between_settlement_and_maturity divided by the number_of_days_in_a_year.
Now:
maturity _value = redemption_value + interest
= 100 + 100*rate*YEARFRAC(issue;maturity)
= 100*(1+rate*YEARFRAC(issue;maturity))
where rate is the annual interest rate.
settlement_value can be found from the equation for PRICEMAT:
price = presentvalue_of_redemption_and_interest - accrued_interest
= settlement_value - accrued_interest
=> settlement_value = price + accrued_interest
= price + 100*rate*YEARFRAC(issue;settlement)
From above:
1 + yield*YEARFRAC(settlement;maturity) = maturity _value / settlement_value
=> yield = (maturity _value / settlement_value - 1)/YEARFRAC(settlement;maturity)
= (100*(1+rate*YEARFRAC(issue;maturity)) / (price + 100*rate*YEARFRAC(issue;settlement)) - 1)/YEARFRAC(settlement;maturity)
where all the YEARFRACs are calculated using the correct basis.
