Excel: Financial Functions

1
Excel Financial Functions

• David Turton
• Conestoga College
• Institute of Technology Advanced Learning
• http//www.conestogac.on.ca/dturton
• Doon 1B43 x3610

2
Financial Functions

• Money appreciates in value over time
• Investments should give same or better return
  than the bank otherwise, why invest?
• Companies and individuals have an idea of the
  interest their money should earn each year.
• Any activity that doesnt meet the expected rate
  of return is not worth investing in
• there are better things to do with your money.

3
5 Components of Financial Functions

• Present value
• Future value
• Interest rate per payment period
• Number of payment periods
• Payment amount
• Note the payment period base Excel assumes
  interest is compounded to the same base (monthly
  payments interest compounded monthly)

4
Need one? Must have other 4

• PV - present value
• FV - future value
• PMT - payment
• NPER - number of payment periods
• RATE - interest rate
• Note cash in is positive, while cash out is
  negative (or vice-versa) so the initial loan
  amount is positive, while the payments and future
  value (aka last payment) are negative.

5
Example find monthly income20,000 initial,
5 investment interest, 2 years,need 2,000 at
end for clothes apartment deposit
Annual interest divide to get monthly rate
12 payments/yr, 2 years 24 payments
Present/initial value
Future/last payment value
6
TimeLines

• Not everything is nice and orderly
• Constant payments over a period of time
• Many projects have up-front costs, annual
  maintenance and future savings
• Best if you visualise cash flows against a
  timeline

7
TimeLine Example

• Development project
• Costs
• 75,000 first year - development
• 10,000 second year - training
• 10,000 second subsequent - maintenance
• Benefit
• 40,000 savings, second year
• 80,000 savings subsequent years
• Required
• 20 return on investment within 4 years

8
TimeLine Example
1
5
4
3
2
10,000- train 10,000- maint
cost
75,000-
10,000-
10,000-
10,000-
benefit
40,000
80,000
80,000
80,000
PV determined at 20/year
9
FYI Wrap long headings
10
Exercise RRSP

• It is said that if you contribute 100/month from
  age 20 for 15 years, you will have more at 65
  than if you started at age 35 and contributed for
  30 years.
• Prove it
• or determine conditions required for it.