Bond Calculator The bond calculator should generate a value table for a zero cou
ID: 3579156 • Letter: B
Question
Bond Calculator
The bond calculator should generate a value table for a zero coupon bond. A zero coupon bond returns the face falue at the Maturity date and sells at a dicount equal to the present value of the face value. Since there are not interest payments the dates for the valuations are arbitrary. We use monthly in this example.
* Convert the logic on sheet 1 to a VBA subroutine.
-Use the Maturity to calculate the number of months
- Use the annual interest rate to calculate the monthly dicount
- Clear previous values each time the program runs
* Set the code to execute any time one of the parameters (yellow cells) change.
Bond Calculator Amount Annual Interest Rate Maturity (yrs) 100000 6% 1 (Yellow) Month Present Value 0 94190.53 1 94661.49 2 95134.79 3 95610.47 4 96088.52 5 96568.96 6 97051.81 7 97537.07 8 98024.75 9 98514.88 10 99007.45 11 99502.49 12 100000.00Explanation / Answer
Sub bondPrice_cashFlows()
format_cashFlow
Dim faceValue, coupon, cashMaturity, YTM
Dim CashFlow, CouponValue, MaturityValue, cashFlowVal
Dim Rate, Row, Price, MarketRate, col
Dim counter, CashFlowRow, cashFlowCol
Dim ShowCashFlows
frmBondInfo.Show
faceValue = Cells(3, 2)
coupon = Cells(4, 2)
cashMaturity = Cells(5, 2)
YTM = Cells(6, 2)
If Cells(3, 4).Value = “Cash_Flows” Then
ShowCashFlows = 1
End If
CashFlowRow = 4
cashFlowCol = 4
CouponValue = faceValue * coupon
For counter = 1 To cashMaturity
If cashFlowCol > 13 Then
CashFlowRow = CashFlowRow + 1
cashFlowCol = 4
End If
Select Case counter
Case 1 To (cashMaturity – 1)
cashFlowVal = (CouponValue / ((1 + YTM) ^ counter))
Case cashMaturity
cashFlowVal = ((CouponValue + faceValue) / ((1 + YTM) ^ counter))
End Select
If ShowCashFlows = 1 Then
Cells(cashFlowCol, CashFlowRow).Value = cashFlowVal
Cells(cashFlowCol, CashFlowRow).NumberFormat = “$0.00”
End If
Price = Price + cashFlowVal
cashFlowCol = cashFlowCol + 1
Next counter
Cells(9, 2).Value = Price
Cells(9, 2).NumberFormat = “$0.00”
End Sub
'************************************************************************************************************************
Function format_cashFlow()
Range(“a1:h100”).Select
Selection.Clear
Columns(“A:A”).ColumnWidth = 17
Cells(1, 1).Select
ActiveCell.FormulaR1C1 = “Cash flow bond pricing”
With Selection.Font
.Size = 15
.Bold = True
End With
Cells(3, 1).Select
ActiveCell.FormulaR1C1 = “Face Value”
Cells(4, 1).Select
ActiveCell.FormulaR1C1 = “Coupon”
Cells(5, 1).Select
ActiveCell.FormulaR1C1 = “Maturity”</span
Cells(6, 1).Select
ActiveCell.FormulaR1C1 = “Yield to Maturity”
Cells(9, 1).Select
ActiveCell.FormulaR1C1 = “Price”
End Function