Agenda – 4 /17/2013

1. Agenda – 4/17/2013 • Discuss interest and the time value of money • Explore the Excel time value of money functions • Examine the accounting measures of profitability • Course Evaluations

2. Some Excel financial functions

3. Excel Functions are Excel Functions To use them, you must understand the TIME VALUE OF MONEY

4. Understanding time value of money • Money will increase in value over time if the money is invested and can make more money. • If you have $1,000 today, it will be worth more tomorrow if you invest that $1,000 and it earns additional money (interest or some other return on that investment). • If you have $1,000 today, it will NOT be worth more tomorrow if you put it in an envelope and hide it in a drawer. Then the time value of money does not apply as an increase. It will most likely decrease in value because of inflation. Of course, you won’t lose the whole $1,000 either…

5. Introduction to Interest Calculations • When you borrow money you pay interest • When you loan money, you receive interest • When you make a payment • part of the payment is applied to interest • Part of the payment is applied to principal

6. Types of Interest • Simple interest • Interest is paid only on the principal • Many certificates of deposit work this way • Compound interest • Interest is added to the principal each period • Interest is calculated on the principal plus any accrued interest • Compounding can occur on different periods • Annually, quarterly, monthly, daily

7. Difference between simple and compound interest • Assume that you have $1,000 to invest. $1,000 is the present value (PV) of your money. • You can invest it and receive “simple” interest or you can earn “compound” interest. • The money that you have at the end of the time you have invested it is called the “future value” (FV) of your money.

8. Future value of money • Simple interest is always calculated on the initial $1,000. 5% interest on $1,000 is $50. Always $50. • When interest is paid on not only the principal amount invested, but also on any previous interest earned, this is called compound interest. FV = Principal + (Principal x Interest) = 1000 + (1000 x .05) = 1000 (1 + i) = PV (1 + i)

9. Simple vs. compound interest comparison $1,000 Invested at 5% return

10. How much money would you have if you invested $1000 for 5 years at an interest rate of 5% a year? How much money would you have if you invested $1000 each year for 5 years at an interest rate of 5% a year?

11. Time Value of Money Functions • We are just solving the same equation for a different variable • RATE determines the interest rate • NPER determines the number of periods • PMT determines the payment • PV determines the present value of a transaction • FV determines the future value of a transaction

12. The RATEFunction • Determines the interest rate per period based on • The number of periods • The payment • The present value • The future value • The type

13. The NPERFunction • Determines the number of periods based on • The interest rate • The payment • The present value • The future value • The type

14. Future Value Function FV(rate, nper, pmt, pv, type)

15. If you receive $5000 5 years from now, and the “going” interest rate is 5%, how much is that money worth today?

16. Present Value Function PV(rate, nper, pmt, fv, type)

17. What about if you borrow money? • If you borrow money, the lender wants to earn “compound” money on his/her/its investment. • If you borrow $1000 at 10%, then you won’t pay back just $1,100 (unless you pay it back at once during the initial time period). • You will pay it back “compounded”. Interest will be calculated each period on your remaining balance.

18. Amortization table $1,000 loan, pay $100 year, 5% year interest

19. What would that same amortization table (also called a schedule) look like if the interest was compounded AFTER you paid, rather than BEFORE you paid? (this is a type 1 on Excel financial functions)

20. Amortization table $1,000 loan, pay $100 year, 5% year interest

21. Types of financial questions asked • How much will it cost each month to pay off a loan if I want to borrow $150,000 at 4% interest each year for 30 years? (PMT function) • Assume that you need to have exactly $40,000saved 10 years from now. How much must you deposit each year in an account that pays 2% interest, compounded annually, so that you reach your goal of $40,000? (PMT function) • If you invest $2,000 today and accumulate $2,676.45 after exactly five years, what rate of annual compound interest did you earn? (INTRATE function)

22. Payment function PMT(rate, nper, pv, fv, type)

23. The PMT Function (Example)

24. The IPMT Function (Introduction) • Use IPMT to calculate the interest applicable to a particular period • Use the initial balance for the present value no matter the period • Use PPMT to calculate the principal applicable to a particular period • The arguments to both functions are the same

25. Interest Payment IPMT(rate, per, nper, pv, fv, type)

26. Principal Payment PPMT(rate, per, nper, pv, fv, type)

27. The CUMIPMT Function (Introduction) CUMIPMT calculates the cumulative interest between two periods CUMPRINC calculates the cumulative principal between two periods The arguments to both functions are the same Functions require the analysis tool pack add-in

28. Cumulative Interest Payments CUMIPMT(rate, nper, pv, start_period, end_period, type)

29. Financial questions • If you borrow $1,000 for 5 years and pay 4% yearly interest compounded monthly, how much interest will you pay? • First do the calculation. • Second, what Excel formula would you use to do the calculation for you? • Third, what Excel formula would calculate the payment? • If you invest $1,000 and receive 3% yearly interest compounded quarterly, how much will you have at the end of 10 years?