Function 1
Function 1
| Fin 405 | Score on A3 | 4.00 | ||
| NPV Macros & Functions | ||||
| Score on F1 | 4.00 | |||
| 1. | You are faced with the following contract. You have a bond that does not start paying until sometime in the future. Once it starts paying, it will pay regularly until it matures. It is an amortized payment, so there are no other cash flows involved. Calculate the Present Value of the cash flows. | |||
| a. | Solve the problem using cell formulas below on the spreadsheet. Work out in steps (NOT VBA!) | |||
| b. | Create a VBA Function that Computes the PV of an Annuity that begins sometime in the future. Name the function “MyPV” (or something else if you wish). Functions can ONLY reference the cells in the yellow box! | |||
| Inputs: | APR = | 8% | AR1 | HINT: Total number of payments = years * #pmts in a year |
| Number of payments in a year = | 12 | m1 | ||
| Number of years worth of payments = | 5 | n1 | Suggested variable names | |
| Period in which first cash flow occurs = | 15 | a1 | (The “1” in each name just refers to Problem 1) | |
| Dollar value of Payment = | $240 | p1 | ||
| Note: the correct answer is | $10,785.02 | Enter points off and comments here: | ||
| Function Solution = | ERROR:#NAME? | POINTS OFF: | ||
| 2 | Spreadsheet Solution | |||
| Months | CF | Half credit if half right | ||
| 0 | $10,785.02 | PV at time 0 | ||
| 1 | $0.00 | 2 | VBA | |
| 2 | $0.00 | Half credit if half right | ||
| 3 | $0.00 | |||
| 4 | $0.00 | |||
| 5 | $0.00 | |||
| 6 | $0.00 | |||
| 7 | $0.00 | |||
| 8 | $0.00 | |||
| 9 | $0.00 | |||
| 10 | $0.00 | |||
| 11 | $0.00 | |||
| 12 | $0.00 | |||
| 13 | $0.00 | |||
| 14 | $0.00 | $11,836.42 | PV at time 15 | |
| 15 | $240 | |||
| 16 | … | |||
| 17 | ||||
| 18 | ||||
| 19 |
Function 2
| Score on F2 | 4.00 |
| 2. | Given the cash flow information below and the interest rate (r1), if the first cash flow occurs in period ‘n’, and lasts for ‘m’ periods, then compute the Present Value of the cash flows (value in period n-1). |
| Then compute the PV at period zero assuming the interest rate (r2) applies to the period between now and ‘n’. | |
| a. | Solve the problem using cell formulas below on the spreadsheet. Work out in steps (NOT VBA!) |
| b. | Create a FUNCTION that computes the PV of the cash flow given the periodic rate below. Have your function get the data directly from the green cells. |
| Payment Info: | |
| r1 | 8.00% |
| n | 9 |
| Pmt | 197.52 |
| m | 36 |
| r2 | 6% |
| Excel Solution of PV in n-1 | $2,314.38 |
| Excel Solution of PV in 0 | $14,371.82 |
| Function Solution at period 0 | $14,371.82 |
| Enter points off and comments here: | |
| POINTS OFF: | |
| 2 | Spreadsheet Solution |
| Half credit if half right | |
| 2 | VBA |
| Half credit if half right |
Macro 3
| Score on M1 | 4.00 | ||
| 3. | In this situation, you have a certain amount of money to invest. It will be kept in the investment for a number of years, then withdrawn. You need to compute the value of the withdrawal. | ||
| a. | Solve the problem using cell formulas below on the spreadsheet. Work out in steps (NOT VBA!) | ||
| b. | Create a macro that computes the Future value of a single amount of money without using Excel PV or FV functions. Your Macro needs to retrieve the values in cells F6:F9 and put them into VBA variables. Finally, have the macro write the answer in cell D16. | ||
| Inputs: | APY = | 7% | AY3 |
| Number of periods in a year = | 4 | m3 | Suggested variable names |
| Number of years until withdrawal = | 20 | n3 | (The “3” in each name refers to Macro 3) |
| Present Value = | $2,152 | pval3 | |
| Enter points off and comments here: | |||
| The correct answer is | $8,327.56 | POINTS OFF: | |
| 2 | Spreadsheet Solution | ||
| Periodic rate | 1.7059% | Half credit if half right | |
| FV = | $8,327.56 | ||
| Macro Result = | 2 | VBA | |
| Half credit if half right |
Macro 4
| Score on M4 | 4.00 | |
| 4. | You have a deferred annuity that is worth $1,000 today. The annuity starts its first payment 15 periods from now. Other information is given below. Compute the Payment of the annuity. | |
| a. | Solve the problem using cell formulas below on the spreadsheet. Work out in steps (NOT VBA!) | |
| b. | Create a VBA Macro that Computes Payment of the annuity. Have the Input Boxes ask for the following input: | |
| What is the Present Value of the Annuity? | Default = | $ 1,000 |
| What is the EAY? | Default = | 8% |
| How many periods in a year? | Default = | 12 |
| How many PAYMENTS in the analysis? | Default = | 60 |
| How many periods from now do the cash flows begin? | Default = | 15 |
| The correct answer is | $ 22.0353 | |
| Spreadsheet solution | ||
| Monthly rate | 0.64% | |
| FV of PV | $1,093.94 | |
| $22.0353 | ||
| Macro solution | ||
| Enter points off and comments here: | ||
| POINTS OFF: | ||
| 1 | Input boxes working | |
| 1 | Correct rate conversion | |
| 1 | Correct PV | |
| 0.5 | Others |