259 Lecture 2 Spring 2013

1. 259 Lecture 2 Spring 2013 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 interestrate of r%, for a time period t years, then the future value or maturity value of the principal P is given by • A = P(1+r*t). • Note that the interest is given by • I = P*r*t.

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 the Data tab and choose What-If Analysis from the Data Tools group 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 B2:B4 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 called “Loan 2” with appropriate 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 valueP can be found from the future value formula above by solving for principal P: • P = A/(1+r*t)

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?

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/(1+r*t). • We find P = 6000/(1+0.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 Excel’s Goal Seek tool, which attempts to solve problems with one variable.

15. Goal Seek • Reset cell B2 to a principal of 5000 dollars. • Click on What-If Analysis=>Goal 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! • How about if we want to have the entire tuition payment in 4 months? • Repeat with a time period of 4 months to get present value of $5940.59.

17. Goal Seek (cont.) • Now, using the 4 month solution we just found, try starting with an interest rate of 8% and changing the time period (via Goal Seek) to get a future value of A = $6000. • Note that Goal Seek requires a value (i.e. number), not a formula (such as =4/12) in the changing cell.

18. 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!

19. 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*(1+0.05) dollars • After 2 years, the account will have: 10,000(1+0.05) + 10,000(1+0.05)*(0.05) = 10,000*(1+0.05)2 dollars • … • After n years, the account will have: 10,000(1+0.05)n dollars.

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

21. 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?

22. Compound Interest (cont.)

23. Annuities and Amortization • We’ll look at these next time!

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