In: Finance
Utilizing the excel examples in Blackboard create and excel file that answers NPV, IRR, Payback for the following situation.
Answer: | ||||||||
A) Calculation of NPV | ||||||||
B) Calculation of IRR | ||||||||
Formula = | Lower rate + | NPV at lower rate | x (Higher rate - Lower rate) | |||||
NPV at lower rate - NPV at higher rate | ||||||||
NPV = | Present value of Cash inflows - Initial cost | |||||||
Initial cost = | $1000.00 | |||||||
Present value at lower rate = | 5.00% | Present value at higher rate = | 25% | |||||
Year | Cash Inflows | Present value factor @ 5% | Present value of cash inflows | Year | Cash Inflows | Present value factor @ 25% | Present value of cash inflows | |
1 | 300.00 | 0.95238 | 285.71 | 1 | 300.00 | 0.80000 | 240.00 | |
2 | 300.00 | 0.90703 | 272.11 | 2 | 300.00 | 0.64000 | 192.00 | |
3 | 300.00 | 0.86384 | 259.15 | 3 | 300.00 | 0.51200 | 153.60 | |
4 | 300.00 | 0.82270 | 246.81 | 4 | 300.00 | 0.40960 | 122.88 | |
5 | 300.00 | 0.78353 | 235.06 | 5 | 300.00 | 0.32768 | 98.30 | |
6 | 300.00 | 0.74622 | 223.86 | 6 | 300.00 | 0.26214 | 78.64 | |
7 | 300.00 | 0.71068 | 213.20 | 7 | 300.00 | 0.20972 | 62.91 | |
8 | 300.00 | 0.67684 | 203.05 | 8 | 300.00 | 0.16777 | 50.33 | |
Total | 1938.96 | Total | 998.67 | |||||
NPV at Lower rate | NPV at higher rate | |||||||
Present value of cash inflows | 1938.96 | Present value of cash inflows | 998.67 | |||||
Less: Initial cost | 1000.00 | Less: Initial cost | 1000.00 | |||||
NPV | 938.96 | NPV | -1.33 | |||||
IRR | 5% | 938.9638 | x (25-5)% | |||||
940.2904 | ||||||||
IRR | 5% | 0.9986 | *20% | |||||
IRR | 5% | 19.9718 | % | |||||
IRR | 24.97% | |||||||
or 24.95% | (Answer) | |||||||
A) Calculation of NPV = | $938.96 | |||||||
B) Calculation of IRR = | 24.95% |
C) Calculation of payback period | ||||||
Formula | ||||||
Payback period = Year before full recovery of cost + Balance cost to be recovered/cash inflow during the year | ||||||
Calculation of cummulative cash inflow | ||||||
Year | Cash inflows | Cummulative inflows | ||||
1 | 300.00 | 300.00 | ||||
2 | 300.00 | 600.00 | ||||
3 | 300.00 | 900.00 | ||||
Related SolutionsIN EXCEL< SHOW THE FORMULAS > Determine the Payback period, NPV and IRR for both project...IN EXCEL< SHOW THE FORMULAS > Determine the Payback
period, NPV and IRR for both project A and B (show work). Which
Project would you select and why? Be specific. Project A will
require an initial investment of $ 200,000 and Project B will
require and initial investment of $ 325,000. The cost of capital
for both projects is 12%. The cash inflows for the next 5 years are
listed below:
Project A Project B
0 ($200,000) ($325,000)
1 $50,000...
Please create an Excel file with the calculated answers to the questions to the Excel homework...Please create an Excel file with the calculated answers to the
questions to the Excel homework for this week. The questions are
listed in the attached PDF file.
Please also go over the Excel file attached to this assignment
in order to familiarize yourself with the different ways Excel can
be used to solve Time Value of Money problems when multiple cash
flows are involved. There are three worksheets in the Excel file.
This Excel file with examples is just...
Explain how to calculate the capital budgeting criterion (in excel) : NPV, IRR, MIRR, Payback, Discounted...Explain how to calculate the capital budgeting criterion (in
excel) : NPV, IRR, MIRR, Payback, Discounted Payback, Crossover
Rate, and decide between mutually exclusive and/or independent
projects
Problem #4 Calculate NPV, Payback, Discounted Payback, IRR and Modified IRR for the following project Initial...Problem #4
Calculate NPV, Payback, Discounted Payback, IRR and Modified IRR
for the following project
Initial Investment: -100,000
Annual project cash flow 22,000 for 6 years
Cost of capital is 6%
You evaluate ALL of its projects by applying the Payback, Discounted Payback, NPV, and IRR rules....You evaluate ALL of its projects by applying the Payback,
Discounted Payback, NPV, and IRR rules.
Assume the cost of capital is 10%.
Assume cash flows of:
TIME
CASH FLOWS
--------------------------------------------------------------
0
-$100
1
+$75
2
+$50
3
+$25
What is the payback?
What is the Discounted Payback?
What...
While NPV and IRR are measures of return of a project/investment. Payback is a measure of...While NPV and IRR are measures of return of a
project/investment. Payback is a measure of risk. Explain what
payback tells about a project/investment and how is it
calculated.
How does the NPV, IRR and the payback period of an Investment usually react to the...How does the NPV, IRR and the payback period of an Investment
usually react to the following developments (please indicate,
UP/DOWN or N/A)?
1. Increasing Investment amount
2. Increasing tax rate
3. Increased annual depreciation due to change of depreciation
method
4. Increasing cost of capital
5. Increasing residual value
So I actually Need 15 indications (5*3) - TIA
. Complete problems: NPV, IRR, MIRR, Profitability Index, Payback, Discounted Payback A project has an initial.... Complete problems: NPV, IRR, MIRR, Profitability Index,
Payback, Discounted Payback A project has an initial cost of
$60,000, expected net cash inflows of $10,000 per year for 8 years,
and a cost of capital of 12%. Show your work. a. What is the
project’s NPV? (Hint: Begin by constructing a timeline). b. What is
the project’s IRR? c. What is the project’s MIRR? d. What is the
project’s PI? e. What is the project’s payback period? f. What is...
Calculate the five different criteria for evaluating projects (regular payback, discounted payback, NPV, IRR, and MIRR)...Calculate the five different criteria for evaluating projects
(regular payback,
discounted payback, NPV, IRR, and MIRR) for the two projects listed
below. The firm’s
WACC is 9.90%. If the projects are mutually exclusive and the firm
has sufficient budget
available, which project (if any) would you choose to proceed with,
and why? (Hint: you
may want to create the full cash flow table for each project to
fully show your work.)
periods
0
1
2
3
4
project Hay cash...
Question 2. Go to the Blackboard and download the MS excel file, ‘stock_return.xlsx’. It contains a...Question 2. Go to the Blackboard and download the MS excel file,
‘stock_return.xlsx’. It contains a year of monthly stock price data
of Amazon, Pfizer, and S&P 500 (Market Index). Using the data,
answer the following questions. (50 points)
(1) Compute the monthly return of Amazon and Pfizer. You should
get 12 monthly returns for each. To get a monthly return, you need
to use previous month’s stock price. For example, Amazon’s stock
return of 2018-01 will be [(Stock price...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|