Project
milkywayTime
exampleforNPV.xlsxTVM
| Single Cash Flow | ||||||
| Present Value | ||||||
| Inputs | ||||||
| Single Cash Flow | $ 1,000.00 | |||||
| Discount Rate / Period | 6.0% | |||||
| Number of Periods | 5 | |||||
| Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $1,000.00 | |||||
| Present Value | ||||||
| Present Value using the PV Function | ||||||
| Present Value | $747.26 | |||||
| Future Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $747.26 | |||||
| Future Value | ||||||
| Future Value using the FV Function | ||||||
| Future Value | $1,000.00 | |||||
| Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $0.00 | |||||
| Present Value | ||||||
| Perpetuity | ||||||
| Present Value | $16,666.67 | |||||
| Annuity Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | |
| Discount Rate | 0.9433962264 | 0.88999644 | 0.839619283 | 0.7920936632 | 0.7472581729 | |
| Present Value of Each Cash Flow | $943.40 | $890.00 | $839.62 | $792.09 | $747.26 | |
| Present Value | $4,212.36 | |||||
| Annuity Present Value using the PV Function | ||||||
| Present Value | $4,212.36 |
Bond Price
| Bond Pricing | |||||||||||||||||||||
| Semiannual Payments | |||||||||||||||||||||
| Inputs | |||||||||||||||||||||
| Number of Periods to Maturity (t) | 20 | ||||||||||||||||||||
| Face Value (PAR) | $ 1,000.00 | ||||||||||||||||||||
| Discount Rate per Period (r) | 5% | ||||||||||||||||||||
| Coupon Payment (PMT) | $ 60.00 | ||||||||||||||||||||
| Bond Price using a Timeline | |||||||||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Cash Flows | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 60.00 | $ 1,060.00 | |
| Present Value of Cash Flows | $57.14 | $54.42 | $51.83 | $49.36 | $47.01 | $44.77 | $42.64 | $40.61 | $38.68 | $36.83 | $35.08 | $33.41 | $31.82 | $30.30 | $28.86 | $27.49 | $26.18 | $24.93 | $23.74 | $399.50 | |
| Bond Price | $1,124.62 | ||||||||||||||||||||
| Bond Price using the PV Function | |||||||||||||||||||||
| Bond Price | $1,124.62 |
IRR & NPV
| NPV and IRR | |||||||||||
| Discount Rate per Period (r) | 12% | ||||||||||
| Timeline | |||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cash Flows | ($12,950.00) | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 | $ 3,000.00 |
| NPV using the NPV function | |||||||||||
| NPV | $4,000.67 | ||||||||||
| IRR using the IRR function | |||||||||||
| IRR | 19.15% | ||||||||||
| NPV is Additive | |||||||||||
| Discount Rate per Period (r) | 10% | ||||||||||
| Timeline | |||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | |||||
| Cash Flows | ($250,000.00) | $ 110,000.00 | $ 60,000.00 | $ 40,000.00 | $ 80,000.00 | $ 22,500.00 | |||||
| NPV using the NPV function | |||||||||||
| NPV | ($1,748.82) |
Assuming a project with the following assumptions: Initial investment = $12,950 Project life = 10 years Cash Flows per year = $3000 Discount rate = 12% NPV returns $4000.67. IRR is 19.15%.
Assuming another project with the following assumptions: Initial investment = $250,000 Project life = 5 years Cash Flows per year = See above Discount rate = 10% NPV returns -$1748.82. You would not combine the two projects even if though the combined NPV is positive. You will only pick the first project.
Bond Pricing, Other Elements
| Bond Pricing | |
| Inputs | |
| Annual Coupon Rate | 8.00% |
| Number of Payments / Year | 1 |
| (1) Number of Periods to Maturity (T) | 10 |
| (2) Face Value (PAR) | $1,000.00 |
| (3) Discount Rate / Period (r) | 10.00% |
| (4) Coupon Payment (PMT) | $80.00 |
| (5) Bond Price (P) | $877.11 |
| (1) Number of Periods to Maturity (T) | |
| Use NPER | 10 |
| (2) Face Value (PAR) | |
| Use FV | $1,000.00 |
| (3) Discount Rate / Period (r) | |
| Use RATE | 10.00% |
| (4) Coupon Payment (PMT) | |
| Use PMT | $80.00 |
| (5) Bond Price (P) | |
| Use PV | $877.11 |
Assume a bond maturing in 10 years with a coupon rate of 8%. The par value is $1000. Investor's required return is 10%. The price of this bond is $877.11.
Dvident Discount Model
| Stock Valuation | |||||||
| Inputs | |||||||
| Cost of Equity | 12% | ||||||
| Dividend Discount Model | Growth Stage | Constant Growth Stage | |||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Dividend Growth Rate | 12.0% | 11.0% | 10.0% | 9.0% | 8.0% | 7.0% | |
| Dividend | $5.90 | $6.61 | $7.33 | $8.07 | $8.79 | $9.50 | $10.16 |
| Continuation Value | $203.26 | ||||||
| Dividend + Continuation Value | $6.61 | $7.33 | $8.07 | $8.79 | $212.76 | ||
| Present Values | $5.90 | $5.85 | $5.74 | $5.59 | $120.72 | ||
| Stock Value | $143.80 | ||||||
Assume that currently a stock pays a dividend per share of $5.90. The firm expects the growth over the next 5 years as outlined below. The firm's cost of equity is 12%. What is the stock's value?
