Please help me, I will rate highest possible rating and points to whoever can so
ID: 2647934 • Letter: P
Question
Please help me, I will rate highest possible rating and points to whoever can solve this problem using EXCEL!
The ABC Company has $20 Million ($20,000,000) it wants to invest, and is trying to decide what to do. They have an investment that will pay them 7.5% annual interest into which they can put the entire $20 Million today. They also are considering putting $15 million into a 4 year joint venture, which is expected to yield them at the end of each year:
Year 1 - $3 Million
Year 2 - $4 Million
Year 3 - $5 Million
Year 4 - $6 Million
If they choose the joint venture, they can invest the remaining $5,000,000 into the 7.5% investment (today), and then invest the annual returns at the end of each year as they receive them.
Use the Net Present Value (NPV) function in Excel to determine which strategy the ABC Company should choose.
Explanation / Answer
Cost of capital or discount rate is not separately given in the question. So we will assume 7.5% interest rate as a discount rate for calculation of Net present value.
Option - 1
They have an investment that will pay them 7.5% annual interest into which they can put the entire $20 Million today.
(Figures in $ million)
Year
Principal
Interest^
cashflows
Discount rate =7.5%
PV of cashflows
0
-20
0
-20
1
-20
1
0
1.5
1.5
0.930232558
1.40
2
0
1.5
1.5
0.865332612
1.30
3
0
1.5
1.5
0.80496057
1.21
4
20
1.5
21.5
0.74880053
16.10
Net Present value
0
^20 * 0.075
Net present value under Option 1 = $ 0 million
Option-2
(a) Calculation for investment of $ 15 million
(Figures in $ million)
Year
Investment
Income
cashflows
Discount rate =7.5%
PV of cashflows
0
-15
0
-15
1
-15
1
0
3
3
0.930232558
2.79
2
0
4
4
0.865332612
3.46
3
0
5
5
0.80496057
4.02
4
0
6
6
0.74880053
4.49
Net Present value
-0.23
(b) Calculation for investment of $ 5 million
Interest calculation
(Figures in $ million)
Year
Opening bal
Interest^
Closing bal
1
5
0.38
5.38
2
5.38
0.40
5.78
3
5.78
0.43
6.21
4
6.21
0.47
6.68
^Opening bal *0.075
(Figures in $ million)
Year
Investment
Income
cashflows
Discount rate =7.5%
PV of cashflows
0
-5
0
-5.00
1
-5
1
0
0.38
0.38
0.930232558
0.35
2
0
0.40
0.40
0.865332612
0.35
3
0
0.43
0.43
0.80496057
0.35
4
5
0.47
5.47
0.74880053
4.09
Net Present value
0.14
Total Net present value for option 2 = (a) + (b)
= $ -0.23 million + $ 0.14 million
= $ -0.09 million
So The ABC Company should choose Option 1 (i.e. investment that will pay them 7.5% annual interest into which they can put the entire $20 Million today).
Year
Principal
Interest^
cashflows
Discount rate =7.5%
PV of cashflows
0
-20
0
-20
1
-20
1
0
1.5
1.5
0.930232558
1.40
2
0
1.5
1.5
0.865332612
1.30
3
0
1.5
1.5
0.80496057
1.21
4
20
1.5
21.5
0.74880053
16.10
Net Present value
0
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.