### Best writers. Best papers. Let professionals take care of your academic papers

Order a similar paper and get 15% discount on your first order with us
Use the following coupon "FIRST15"
ORDER NOW

# Function 1

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