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.

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

EFFECTIVE; EFFECT_ADD; (EFFECT)

An investment is currently worth p, and pays interest at a rate of r each period.

At the end of the first period:

The interest due = pr

The value of the investment = p + pr = p(1 + r)

At the end of the second period:

The interest due = p(1 + r)r

The value of the investment = p + p(1 + r)r = p(1 + r)²

Each period multiplies the value of the investment by (1 + r)

At the end of the nth period:

The value of the investment = p(1 + r)ⁿ

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

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

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:

1 + yield*YEARFRAC(settlement;maturity) = maturity _value / settlement_value.

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