spread sheets use as examples the first two and fill out last one

Week 04 Example Problems

Present Value and Future Value using Excel Functions

Please download the Week 04 Example Problems Excel spreadsheet to solve the following:

Present Value

To calculate present value, we will use the built-in PV feature in Excel.

Following are the values that we will use in the Example problem on the Present Value sheet:

Rate (Interest rate)	6%
Nper (Number of periods)	5
PMT (Payment)	0
FV (Future value)	$100
Type	0

Click on the PV cell to see how the formula was entered. You may notice that the solution came up as a negative number, this is because cash flows out are recognized as negatives. This is easy to correct if you would like, simply enter a negative before the FV while entering the formula.

Compare this to this week's material that demonstrates this concept using a financial calculator All you need to know to "translate" those directions is that N, number of periods is Nper, also meaning number of periods in Excel and i, interest rate, is Rate, meaning interest rate, in Excel. The rest is precisely the same. Keep PMT and Type zero for the PV and FV problems.

Try using this same formula to calculate the new balance for Example 1A and 1B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Future Value

To calculate future value, we will use the built-in FV feature in Excel.

Following are the values that we will use in the Example problem on the Future Value sheet:

Rate (Interest rate)	5%
Nper (Number of periods)	20
PMT (Payment)	0
PV (Present value)	$-100
Type	0

We use the FV function in Excel to solve this problem. Click on the FV cell to see how the formula was entered.

Try using this same formula to calculate the new balance for Example 2A and 2B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Interest

Click on the Interest tab to complete the following examples:

Simple Interest

In the Example 1 problem, there is $100 of principal, and an annual interest rate of 5%. To calculate the ending balance, first we calculate the interest (principal x rate x time), then add it to the principal. We now have a new balance of $105. If you click in the cells, you can see the formulas that were used.

Try using this same formula to calculate the new balance for Example 3A and 3B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Compound Interest

The text explains these problems using the future value tables. In our practice activities, we will use the Power function in Excel. Powers are quick ways to multiply repeatedly rather than calculating the interest 20 times.

In Example 2, we will calculate the future value of $100 that is invested for 20 years at 5%, compounded annually.

To calculate using this model:

1. We first figure 1 + interest rate (1.05)

2. Then calculate the Power at 20 periods (2.653298) (Use the POWER function)

3. To figure the end result, we multiply the $100 (present value) X the Power result (2.653298), to end up with $265.33.

As an alternate model, we could use the Future Value function built into Excel. In Example 3, this method has been utilized.

1. Insert the function, FV, the rate is 5%

2. Nper is the 20 periods

3. PV is the $100

4. Leave the other areas blank for this example

You will see that the same answer appears as in Example 1. One thing that you may notice is that the answer appears as a negative because it is an outflow of cash. To change this, simply enter a negative before the $100.

Try using this same formula to calculate the new balance for Example 4A and 4B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Annuities

Present Value of an Annuity

Click on the PV Annuity worksheet, in the Example you are expecting to receive $100 payments at the end of each year for three years and the rate is 6% on invested funds. We use the same PV formula, but add more information. Click on the PV cell to see how the formula was entered. The difference between an ordinary annuity and an annuity due is when the payment is made. If the payment is made at the beginning of the period, it is an annuity due. If made at the end of the period, it is an ordinary annuity. The only change in your Excel formula is that you change the type to a "1" rather than a "0".

Try using this same formula to calculate the new balance for Example 5A and 5B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Future Value of an Annuity

Click on the FV Annuity worksheet, in the Example you trying to calculate how much will be in your savings account if you deposit $100 payments at the end of each year for three years and the rate is 5% on savings. We use the same FV formula, but add more information. Click on the FV cell to see how the formula was entered.

Try using this same formula to calculate the new balance for Example 6A and 6B. There will be a note next to the cell that will inform you if the answer is "correct" or "incorrect." Keep working the problems until you have the correct answer.

Adapted from:

Mayo, H. (2007). Basic finance: An introduction to financial institutions, investments & management. United States: Thomson South-Western.