My issue: The following question I have the answer to, but my issue is that in Part 5 it references Cf's and NF's, I am not sure how they got these numbers also it only references a financial calculator in Part 5, can this part be calculated via Excel? Thank you

Suppose Hadden Inc. is negotiating with an insurance company to sell a bond issue. Each bond has a par value of $1,000, it would pay 10 percent per year in quarterly payments of $25 per quarter for 10 years, and then it would pay 12 percent per year ($30 per quarter) for the next 10 years (Years 11-20). The $1,000 principal would be returned at the end of 20 years. The insurance companys alternative investment is in a 20-year mortgage which has a nominal rate of 14 percent and which provides monthly payments. If the mortgage and the bond issue are equally risky, how much should the insurance company be willing to pay Hadden for each bond? (Hint: You will need a financial calculator to work this problem.)

a. $750.78 b. $781.50 c. $804.65 d. $710.49 e. $840.97

Answer is:

1. You could enter the time line values into the cash flow register, but one element is missing: the interest rate. Once we have the interest rate, we could press the NPV key to get the value of the bond.

2. We need a periodic interest rate, and it needs to be a quarterly rate, found as the annual nominal rate divided by 4: r_PERr = r_NOM/4. So, we need to find r_Nom so that we can find r_Per.

3. The insurance company will insist on earning at least the same effective annual rate on the bond issue as it can earn on the mortgage. The mortgage pays 14 percent monthly, which is equivalent to an EAR = 14.93%. Using a financial calculator, enter NOM% = 14, P/YR = 12, and press EFF% to obtain 14.93%. So, the bond issue will have to have a rNom, with quarterly payments, which translates into an EAR of 14.93 percent.

4. EAR = 14.93% is equivalent to a quarterly nominal rate of 14.16 percent; that is, a nominal rate of 14.16 percent with quarterly compounding has an EAR of 14.93 percent. You can find this by entering EFF% = 14.93, P/YR = 4, and pressing the NOM% key to get NOM% = 14.16%. If this nominal rate is set on the bond issue, the insurance company will earn the same effective rate as it can get on the mortgage. (Dont forget to set your calculator back to P/YR = 1.)

5. The periodic rate for a 14.16 percent nominal rate, with quarterly compounding, is 14.16%/4 = 3.54%. This 3.54% is the rate to use in the time line calculations.

With an HP-10B calculator, enter the following data: CF0 = 0, CF_j = 25; N_j = 40; CF_j = 30; N_j = 39; CF_j = 1030; I = 3.54. Solve for NPV = $750.78 = Value of each bond.

With an HP-17B calculator, enter the following data: Flow(0) = 0 Input; Flow(1) = 25 Input; # Times = 40 Input; Flow(2) = 30 Input; # Times = 39 Input; Flow(3) = 1030 Input; # Times = 1 Input; Exit; Calc; I = 3.54. Solve for NPV = $750.78 = Value of each bond.

If each bond is priced at $750.78, the insurance company will earn the same effective rate of return on the bond issue as on the mortgage.