Page 1 of 5
Chapter 3
Financial Functions
Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.
 Understanding Percentages
Percentages are something familiar to us all  but they present many pitfalls that need to be avoided.
 Interest Simple and Compound
We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment. The ideas of Present and Future Value PV and FV are introduced.
 Effective Interest Rates
We explore the idea of the `effective’ annual interest rate and then on to the Effective Interest Rate/Annual Percentage Rate, the much quoted EIR or APR.
 Introduction to Cashflow  Savings Plans
In the first of three chapters covering the way in which interest rate affects cashflow we explore savings  but first we introduce some general ideas that apply equally to annuities and repayment loans.
 Cashflow Continued  Annuities
We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects
 Exploring Repayment Loans
Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.

Present and Future Values The principles of present and future value apply even if the cash flow is irregular. The calculations are just a matter of breaking down the cash flow calculations into simple steps.

Investment analysis How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.

IRR The Internal Rate of Return The IRR is perhaps the most complicated of the measures of the value of an investment with an irregular cash flow. Understanding exactly what it means is a good step toward making correct use of it.
In the previous chapter we discovered that compound interest is nothing more than the repeated application of simple interest to the accumulated total of the principal and the interest to date. This is such a common form of financial transaction that we tend to treat it as something special. In particular, we like to summarise the effect that compounding has on the underlying or nominal interest rate.
This leads us to the idea of the `effective’ annual interest rate and then on the formalised Effective Interest Rate or Annual Percentage Rate, the much quoted EIR/APR.
As well as changes to effective rates due to compounding, there is also the question of how the effect of tax and tax allowances can be summarised within an interest rate. This brings us to the idea of nett and gross rates.
Finally we consider some issues of working with different compounding periods.
The effective rate
If you make an investment with the interest reinvested then it is clear that the effect of compounding will result in you being paid more interest at the end of the year than the simple nominal annual rate would suggest.
For example, if you invest $100 at a nominal rate of 10% per annum compounded monthly, at the end of one year you will have earned $110.47, not the $110 that the nominal interest might lead you to believe.
Because of compounding the effective interest rate per annum appears to be 10.47% and this is what a Bank or other institution might consider is the annual interest worth quoting because it allows for the compounding that will almost certainly occur.
This is the effective, or actual, annual rate of interest that applies to an investment  as long as the interest isn’t withdrawn each month.
The same sort of reasoning applies to loans. In this case the effective or actual rate of annual interest applies if the interest on the debt is rolled up into the amount borrowed and hence owed. If you pay the interest on the loan monthly then the rate that applies is the nominal rate.
The effective rate is also often quoted as 'so many percent compound’ as in 10.9% per annum compound. This method of presentation makes it quite clear that what is being quoted is a rate that results if and only if you leave the interest to attract further interest.
Also notice that in this case it isn’t even important that the compounding period is quoted  what is at issue is the effective rate.
Calculating the effective rate
Clearly the effective, or actual, annual interest rate is an important quantity and it is worth knowing how to calculate it in general.
The value of the investment at the end of one year is simply the future value and so the total amount earned in interest is the future value minus the present value.
Putting this another way the Future Value FV is related to the Present Value by:
FV=(1+EI)*PV
This is the fundamental equation of finance. The FV can always be regarded as the PV increased by EI% in one interest earning period.
If you know the FV and the PV you can rearrange the equation to five the interest rate  the effective interest rate over a single investment period That is:
effective annual rate=EI=(FVPV)/PV
or
=FV/PV1
Now you can us the fact that the FV is given by the application of the monthy interest rate:
FV=PV*(1+I)^n
and now you have a formula that connects I the monthy rate and EI the effective annual rate.
EI=PV*(1+I)^n / PV 1
If you want to you can make use of this formula directly in a spreadsheet although it isn’t the most efficient way of doing the calculation.
If you try it out you will discover that you get the same result no matter what PV is. This is entirely reasonable because there is no reason why the effective annual rate should depend on the present value. If you set PV to $1 (or by writing the formula out in full and cancelling out the principal) then you get a the simplest formula possible for the effective annual rate:
=(1+I)^n  1
where I is the interest rate per compounding period and n is the number of compounding periods in one year.
As the most common compounding period is one month the relation between the nominal annual rate and the effective annual rate is:
A=(1+I/12)^121
where I is the nominal annual interest rate and A the effective or actual annual rate.
For example, if the nominal annual interest rate for savings is 10% and the interest is calculated and added monthly then the effective annual rate is:
(1+0.01/12)^121
because there are 12 compounding periods per annum  this works out to 10.47% after rounding to two decimal places.
The effective annual rate is also called the Compounded Annual Rate or CAR.
If you need to work out the nominal annual rate given the effective annual rate i.e. to go back to the rate before compounding, this is:
I=((I+A)^1/121)*12
Nominal and effective  a summary
In case you are getting lost in all of this discussion of nominal and effective interest rates, a summary might prove useful:
 The nominal rate of interest is the rate without taking the effect of compounding into account.
 To convert from the annual nominal rate to the rate that is applied at each calculation and addition of interest, simply divide by the number of interest periods in a year.
 If the interest paid is added to the principal then the effect of compound interest can be summarised in the effective annual rate.
Looked at yet another way, the effective annual rate tells you by what percentage the principal will grow in a year taking compounding into account.
<ASIN: 0984849009>
<ASIN: 1477463992>
<ASIN:1118117859>
