299X159 Lecture 2

1
299X-159 Lecture 2

• Finance Applications with Excel Simple and
  Compound Interest

2
Finance Applications

• Excel is a useful tool for working with financial
  applications that arise in areas such as
  business, economics, or actuarial science,
  including
• Simple Interest
• Compound Interest
• Annuities
• Amortization

3
Simple Interest

• In order to borrow (invest) money from a bank, we
  have to pay (are paid) interest on the money,
  which is usually a percentage of the amount
  borrowed (invested).
• Simple interest is a type of interest that is
  paid only on the amount borrowed (invested).

4
Simple Interest (cont.)

• If we deposit P dollars at an annual interest
  rate of r, for a time period t, then the future
  value or maturity value of the principal P is
  given by
• A P(1rt).
• Note that the interest is given by
• I Prt.

5
Simple Interest (cont.)

• Example 1 For which of the following loans
  would we end up paying less interest?
• (a) 10,000 borrowed for 1 year at 7 interest.
• (b) 9,000 borrowed for 11 months at 8 interest.

6
Simple Interest (cont.)
7
Using Scenarios

• We can also use Scenarios to compare loans!
• Construct a Simple Interest Calculator in
  Excel!
• Click on Tools gt Scenarios to pull up the
  Scenario Manager.

8
Using Scenarios (cont.)

• In the Scenario Manager, choose Add to add a
  new scenario.
• Choose Loan 1 as the Scenario name and choose
  cells B2B4 as the Changing cells.
• Change any cell values you wish for the scenario
  and click OK.

9
Using Scenarios (cont.)

• Repeat the above steps to add more scenarios.
• Add a Scenario Loan 2 with the data from Example
  1.
• Use 0.9167 or 11/12 for the time period instead
  of 11/12. (Why?)
• Choose Summary to get a summary table of all
  the scenarios!

10
Using Scenarios (cont.)
11
Present Value

• Suppose we wish to have a certain amount of money
  at a future date, based on money deposited today.
• The amount needed today is called the present
  value of the future amount.

12
Present Value (cont.)

• If future amount A is obtained by investing
  amount P today at simple interest rate r for t
  years, then present value P can be found from the
  future value formula above by solving for
  principal P
• P A/(1rt)

13
Present Value (cont.)

• Example 2 Tuition of 6000 will be due when the
  spring semester starts in 5 months.
• What amount should be deposited today at 3
  interest to have enough to cover the tuition?
• How about if we want to have the entire tuition
  payment in 4 months?

14
Present Value (cont.)

• One way to solve this problem is to directly
  calculate the present value of the 6000 using
  the formula P A/(1rt).
• We find P 6000/(10.03(5/12)) 5925.93
  dollars.
• Another way is to guess choices for principal P
  in the Simple Interest Calculator we made in
  Excel until the future value A is 6000.
• A third way is to use the Excels Goal Seek tool,
  which attempts to solve problems with one
  variable.

15
Goal Seek

• Click on ToolsgtGoal Seek.
• In the Goal Seek dialog box, Set cell B5, To
  value 6000, By changing cell B2.
• Click OK and Excel will try to find a solution
  iteratively.

16
Goal Seek (cont.)

• In this case, a solution is found!
• Choose OK to keep the solution, which is what we
  calculated by hand above!
• Repeat with 4 months to get present value of
  5940.59.
• Note that to use Goal Seek, you may have to
  change the initial data.
• Try starting with the solution from 5 months and
  changing the time period.
• Then try by starting with interest rate 4/12 and
  changing the principal!

17
Compound Interest

• Simple interest is usually used for loans or
  investments of one year or less.
• For longer investment periods, compound interest
  is used.
• In this case, interest is charged (paid) on both
  interest and principal!

18
Compound Interest (cont.)

• Suppose you put 10,000 into a bank account
  earning 5 annual compound interest.
• After 1 year, the account will have
  10,000 10,000(0.05) 10,000(10.05)
  dollars
• After 2 years, the account will have
  10,000(10.05) 10,000(10.05)(0.05)
  10,000(10.05)2 dollars
• After n years, the account will have
  10,000(10.05)n dollars.

19
Compound Interest (cont.)

• In general, if P dollars are deposited for n
  consecutive compounding periods at an interest
  rate i per period, the compound amount A is given
  by
• A P(1i)n.
• Note As before for simple interest, we also
  call P the principal or present value as
  appropriate and call A the future value.

20
Compound Interest (cont.)

• Example 3 Construct a table to compare the
  difference between investing 10,000 at an annual
  rate of 4 for 5 years with compound interest and
  investments where the money is compounded
  annually, quarterly, monthly, daily, and hourly,
  and every minute!
• Which is the best investment?
• Is there much of a difference between the last
  three investments?

21
Compound Interest (cont.)
22
Annuities and Amortization

• Well look at these next time!

23
Homework

• An accountant for a corporation forgot to pay the
  firms tax of 725,896.15 on time. The
  government charged a penalty of 12.7 interest
  for the 34 days the money was late. Find the
  total amount (tax and penalty) that was paid,
  using a 365-day year.

24
References

• Finite Mathematics and Calculus with Applications
  (4th edition) by Margaret L. Lial, Charles D.
  Miller, and Raymond N. Greenwell