Hi Guys,
Iam trying to write a code that calculates individual cash flow and total year cash flow for an portfolio. Ive approached this in a "step by step" excel formula based way. But i suspect its a smarter way to do this.
Please find a screen shot of my sheet here before "processing":
what I need help/suggestions about is how to calculate Cash flow for each bond and year.
A bond pays a coupon as a percentage of face value (its 100) and a principal (its 100) when it expires. (Given my maturity.)
So bond 1, pays coupon of 7 in year 1 and 2, while in year 3 its pays coupon 7 + principal 100.
So for year 1 cell 4G, i need the formula to add only coupon as no bond has expired in year 1. The coupon is 7 for bond 1 as face value is 100, (100x0.07).
While for year 3, in cell 4I i need coupon 7 + 100 principal etc.
It supposed to look like this when calculate: (i typed them in manually):
Here's code we use to calculate bond and principal cash flow
Greatly appreciate any info on what type of code and suggestions.
Iam trying to write a code that calculates individual cash flow and total year cash flow for an portfolio. Ive approached this in a "step by step" excel formula based way. But i suspect its a smarter way to do this.
Please find a screen shot of my sheet here before "processing":
what I need help/suggestions about is how to calculate Cash flow for each bond and year.
A bond pays a coupon as a percentage of face value (its 100) and a principal (its 100) when it expires. (Given my maturity.)
So bond 1, pays coupon of 7 in year 1 and 2, while in year 3 its pays coupon 7 + principal 100.
So for year 1 cell 4G, i need the formula to add only coupon as no bond has expired in year 1. The coupon is 7 for bond 1 as face value is 100, (100x0.07).
While for year 3, in cell 4I i need coupon 7 + 100 principal etc.
It supposed to look like this when calculate: (i typed them in manually):
Here's code we use to calculate bond and principal cash flow
Code:
Sub BondCashflow()
' Input parameters to determine bond cashflow
Principal = Range("B1").Value
Coupon = Range("B2").Value
Maturity = Range("B3").Value
MyYear = Range("B4").Value
' Initialise Cashflow
Cashflow = 0
' One way to compute cashflow
' If MyYear < Maturity Then Cashflow = Coupon
' If MyYear = Maturity Then Cashflow = Principal + CouponGreatly appreciate any info on what type of code and suggestions.
