A company is thinking of investing some surplus cash in 30 year $1000 face value Microsoft 6% annual coupon bonds. It plans to pay $925 each for 10,000 of them, now, and expects to SELL them for $1025 each at the end of 5 years.
Make a determination about the economic viability of the proposal using 3 capital budgeting methods
So how would we break this information down to figure out the simple payback, NPV and IRR using excel?
To determine the economic viability of the proposal using three capital budgeting methods (simple payback, net present value, and internal rate of return), you can break down the information provided and perform calculations in Excel.
1. Simple Payback:
The simple payback method calculates the number of years required to recover the initial investment. The formula for simple payback is:
Simple Payback = Initial Investment / Annual Cash Flow
In this case, the initial investment is the amount paid for the bonds, which is $925 per bond. The annual cash flow is the coupon payment, which is 6% of the face value ($1000) or $60 per bond. To calculate the simple payback, divide the initial investment by the annual cash flow.
2. Net Present Value (NPV):
The net present value method calculates the present value of future cash flows, taking into account the time value of money. The formula for NPV is:
NPV = PV of Cash Inflows - Initial Investment
To calculate NPV, you need to discount the future cash flows back to the present using a discount rate. In this case, we will assume a discount rate of 6% since the bonds have a 6% coupon rate. Using Excel, you can use the NPV function to calculate the net present value.
3. Internal Rate of Return (IRR):
The internal rate of return method calculates the discount rate that makes the NPV zero. It represents the rate of return earned from the investment. To calculate the IRR, use the IRR function in Excel.
To perform these calculations in Excel:
1. Create a column for the number of years (1-5) and label it 'Year.'
2. In the next column, calculate the annual cash flow by multiplying the number of bonds (10,000) by the coupon payment ($60) and label it 'Annual Cash Flow.'
3. Calculate the present value of each annual cash flow by discounting it using the discount rate (6%). Use the PV function in Excel for this calculation.
4. Sum up the present values to get the total present value of cash inflows.
5. Calculate the simple payback by dividing the initial investment ($925 per bond) by the annual cash flow.
6. Use the NPV function in Excel to calculate the net present value, subtracting the initial investment from the total present value of cash inflows.
7. Use the IRR function in Excel to calculate the internal rate of return.
By performing these calculations in Excel, you will determine the simple payback, net present value, and internal rate of return of the proposal.