VBA: Calculate Cash flow per year/per bond for several bonds

[krdale]

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

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 + Coupon

Greatly appreciate any info on what type of code and suggestions.

 

[krdale]

Hi Guys,

I wrote this function to overcome the issue of a VBA Subroutine, it does the job. But let me know if you have any better ideas.

Function Cashflow(CYear, Maturity, Coupon)

'If Maturity = 0 Then

If CYear < Maturity And Maturity <> 0 Then Cashflow = Coupon * 100
If CYear = Maturity Then Cashflow = (Coupon * 100) + 100
If CYear = 0 Then Cashflow = ""
If Cashflow = 0 Then Cashflow = ""