Week 6 – Assignment 2: Calculate Rate of Return
For this assignment, you will build a 1-page Excel spreadsheet to solve the following scenario:
Six years ago, Bradford Community Hospital issued 20-year municipal bonds with a 7% annual coupon rate. The bonds were called today for a $70 call premium—that is, bondholders received $1,070 for each bond. What is the realized rate of return for those investors who bought the bonds for $1,000 when they were issued?
The completed assignment should address all of the assignment requirements, exhibit evidence of concept knowledge, and demonstrate thoughtful consideration of the content presented in the course.
Length: 1 Excel workbook with a 1-page summary explaining your calculations and 3 scholarly/credible references.
For this assignment, you will build a 1-page Excel spreadsheet to solve the following scenario
To solve this scenario, you can use the following steps:
- Calculate the total payment received by bondholders when the bonds were called.
- Determine the total cost of purchasing the bonds when they were issued.
- Calculate the realized rate of return using the formula:
\[ \text{Realized Rate of Return} = \left( \frac{\text{Total Payment Received}}{\text{Total Cost of Purchasing Bonds}} \right)^{\frac{1}{\text{Number of Years}}} – 1 \]
Here’s how you can create an Excel spreadsheet to solve this problem:
- In Excel, create a table with the following columns: “Year”, “Payment Received”, and “Cash Flow”.
- Enter “0” in the “Year” column for the present year and “20” for the maturity year.
- Enter the payment received by bondholders when the bonds were called in the “Payment Received” column. In this case, it’s $1070.
- Enter the cash flow for each year, taking into account the annual coupon payments and the call premium.
- Calculate the total cost of purchasing the bonds when they were issued. It’s $1000 per bond.
- Calculate the realized rate of return using the RATE function in Excel, which calculates the internal rate of return (IRR) of a series of cash flows.
Your Excel spreadsheet should look something like this:
“`
| Year | Payment Received | Cash Flow |
|——|——————|———–|
| 0 | $1,000 | ($1,000) |
| 6 | | $70 |
| 7 | | $70 |
| … | | |
| 20 | $1,070 | $1,070 |
“`
In the “Payment Received” column, you would enter $1070 for the year the bonds were called, and in the “Cash Flow” column, you would enter $70 for each coupon payment year. The “Total Cost of Purchasing Bonds” would be $1000.
Once you have filled in the table, you can use the RATE function in Excel to calculate the realized rate of return. The formula would be something like:
“`excel
=RATE(20, -1000, 70, 1070)
“`
This formula calculates the annualized rate of return over the 20-year period, given the initial investment of -$1000, the $70 annual coupon payments, and the final payment of $1070.
Your 1-page summary should explain your calculations and provide the realized rate of return. Additionally, include references to scholarly/credible sources that support your calculations and concepts used in solving the problem.
"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you "A" results."
