Question

BUSI 320 Comprehensive Problem 3 Spring 2020

*If you could show how you got the answers using EXCEL that would be awesome!* Thanks!

Use what you have learned about the time value of money to analyze each of the following decisions:

Decision #1:   Which set of Cash Flows is worth more now?

Assume that your grandmother wants to give you generous gift. She wants you to choose which one of the following sets of cash flows you would like to receive:

Option A: Receive a one-time gift of $10,000 today.   

Option B: Receive a $1600 gift each year for the next 10 years. The first $1600 would be

     received 1 year from today.                 

Option C: Receive a one-time gift of $20,000 10 years from today.

Compute the Present Value of each of these options if you expect the interest rate to be 2% annually for the next 10 years.    Which of these options does financial theory suggest you should choose?

       Option A would be worth $__________ today.

      Option B would be worth $__________ today.

       Option C would be worth $__________ today.

       Financial theory supports choosing Option _______

       

Compute the Present Value of each of these options if you expect the interest rate to be 5% annually for the next 10 years. Which of these options does financial theory suggest you should choose?

       Option A would be worth $__________ today.

       Option B would be worth $__________ today.

       Option C would be worth $__________ today.

      Financial theory supports choosing Option _______

Compute the Present Value of each of these options if you expect to be able to earn 8% annually for the next 10 years. Which of these options does financial theory suggest you should choose?

       Option A would be worth $__________ today.

       Option B would be worth $__________ today.

       Option C would be worth $__________ today.

       Financial theory supports choosing Option _______

Answer 1

Three options that are given in the above question are

Option 1 = $ 10,000 which will be received today

Option 2 = Receiving $ 1,600 each year for next 10 years

Option 3 = Receiving $ 20,000 at the end of 10 years

We can calculate the desired result using the Excel Sheet as shown below

A) When interest rate is 2% annually for next ten years

The Formulas used in the above sheet are as follows

Using the formulas and for each option, the NPV of each option is

Option A would be worth $ 10,000 today.

Option B would be worth $ 14,372 today.

Option C would be worth $ 16,407 today.

Financial theory supports choosing Option C as it has highest NPV

B) When interest rate is 5% annually for next ten years

Here all the things remain constant, but only the rate of discounting the cashflows changes to 5% as against 2% in above case

The Formulas used in the excel Sheet are all same, except the discounting factor now takes 5% into consideration.

Discounting Factor [1 / (1+5%)^n ] used now.

The NPV of each option is

Option A would be worth $ 10,000 today.

Option B would be worth $ 12,355 today.

Option C would be worth $ 12,278 today.

Financial theory supports choosing Option B as it has highest NPV

C) When interest rate is 8% annually for next ten years

Again here all the things remain constant, but only the rate of discounting the cashflows changes to 8% as against 5% in above case

The Formulas used in the excel Sheet are all same, except the discounting factor now takes 8% into consideration.

Discounting Factor [1 / (1+8%)^n ] used now.

The NPV of each option is

Option A would be worth $ 10,000 today.

Option B would be worth $ 10,736 today.

Option C would be worth $ 9,264 today.

Financial theory supports choosing Option B as it has highest NPV.

Hope, I am able to solve your concern. If you are satisfied hit a thumbs up !!