Documentation/How Tos/Calc: Derivation of Financial Formulas

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)²

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 y^th 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)².

...

At the end of the nth year it is worth p(1 + r)ⁿ.

So the future value after n years is:

f = p(1 + r)ⁿ.

The present value is therefore:

p = f / (1 + r)ⁿ.

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)¹²-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)ⁿ.

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)ⁿ = p(1 + x/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)ⁿ

therefore

e = (1 + x/n)ⁿ - 1

Loans and Annuities

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, p is +$1000 and m is -$50; if we buy an annuity for $6000 which pays $30 regularly, p is -$6000 and m is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (p=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (f=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum p, the periodic payments m and the final sum f.

The future value of p is p(1+r)ⁿ (see Present and Future Values, above).

There are n payments of m:

·When payments are made at the end of each period, the future value of the last payment will be m (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)^n-1. Summing those future values we get m[1 + (1+r) + (1+r)^{2 .....} (1+r)^n-1] = m[(1-(1+r)ⁿ)/(1-(1+r))] = m[(1-(1+r)^x)/(-r)] = m[((1+r)ⁿ-1)/r], using standard result for a geometric series that 1+ a + a² .... a^n-1 = (1+aⁿ)/(1-a).

·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)ⁿ. Summing those future values we get m[((1+r)ⁿ-1)/r](1+r).

·Combining these, where t is the type of payment: t=0 for end of period, t=1 for start of period, the future value of the payments is m[((1+r)ⁿ-1)/r](1+rt).

The future value of f is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

p(1+r)ⁿ + m[((1+r)ⁿ-1)/r](1+rt) + f = 0

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)² - m - m(1+r)

= p(1+r)² - m(1 + (1+r))

At the end of the third period:

interest payable = (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(1+r)r

= p(1+r)² + p(1+r)²r - m - m(1+r) - m(1+r) - m(1+r)r

= p(1+r)³ - m - m(1+r) - m(1+r)²

= p(1+r)³ - m(1 + (1+r) + (1+r)²)

Now

1 + (1+r) + (1+r)²

looks like a geometric series 1+ a + a² .... a^x-1

which = (1+a^x)/(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)ⁿ - m(1 + (1+r) + (1+r)^{2 .....} (1+r)^n-1)

= p(1+r)ⁿ - m((1+r)ⁿ-1)/r

Therefore the formula for PMT is:

m = [p(1+r)ⁿ - f]r / ((1+r)ⁿ-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)² - m - m(1+r) - m(1+r) ²

At the start of the nth period:

capital still owed = p(1+r)^n-1 - m(1 + (1+r) + (1+r)²..... (1+r)^n-1)

= p(1+r)^n-1 - m((1+r)ⁿ-1)/r

At the end of the nth period:

interest payable/owed is (p(1+r)^n-1 - m((1+r)ⁿ-1)/r)r

There is no payment

capital still owed = f

= p(1+r)^n-1 - m((1+r)ⁿ-1)/r + (p(1+r)^n-1 - m((1+r)ⁿ-1)/r)r

= p(1+r)^n-1 - m((1+r)ⁿ-1)/r + p(1+r)^n-1r - m((1+r)ⁿ-1)

= p(1+r)^n-1 + p(1+r)^n-1r - m((1+r)ⁿ-1)/r - m((1+r)ⁿ-1)

= p(1+r)ⁿ - m((1+r)ⁿ-1) (1 + 1/r)

= p(1+r)ⁿ - m((1+r)ⁿ-1) (1 + r)/r

= p(1+r)ⁿ - m((1+r)ⁿ⁺¹-(1+r)) /r

= p(1+r)ⁿ - m(1 + r)((1+r)ⁿ-1)/r

=>

m = [p(1+r)ⁿ - f]r/[(1+r)((1+r)ⁿ-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)ⁿ - m((1+r)ⁿ-1)/r

=>

p = (f + m((1+r)ⁿ-1)/r) / (1+r)ⁿ

If payments are made at the start of each period:

(including at the start of the first period)

f = p(1+r)ⁿ - m(1 + r)((1+r)ⁿ-1)/r

=>

p = (f + m(1 + r)((1+r)ⁿ-1)/r) / (1+r)ⁿ

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.

See also:

Financial functions