1. ## Better Way? (VBA/Excel/97)

I was curious to know if there might be a faster or better way to code the program below. This macro calculates the weighted daily average maturity of a bond portfolio. BondTotal and RemainDays are functions that calculate total bond \$s and the remaining days to maturity of the ith bond respectively. This works, but it is really slow.

Function WeightBond()

Dim Weight As Double
Dim AvgMatBond As Double
Dim RemainDays As Double
Dim i As Long

For i = 1 To range("Price").Count

If range("Maturity_Date")(i) < EndingMonthDate _
Then
Weight = 0

'Calculate days for bonds
ElseIf range("Category")(i) = "b" And _
range("Maturity_Date")(i) > EndingMonthDate _
Then
RemainDays = range("Maturity_Date")(i) - EndingMonthDate - 1

Weight = (range("Book_Value")(i) / BondTotal) * RemainDays

AvgMatBond = AvgMatBond + Weight
End If

Next i

WeightBond = AvgMatBond

End Function

2. ## Re: Better Way? (VBA/Excel/97)

<P ID="edit" class=small>(Edited by JohnBF on 09-Jun-04 13:20. Sorry, I overlooked your explanation that "EndingMonthDate" and "BondTotal" are called from other functions.)</P>Declare and typed (Dim) "EndingMonthDate" and "BondTotal", and post back with those functions.

You might also want to convert this to a userfunction that takes the inputs as arguments like this (sample only):

Function WeightBond(rngPrice as Range, rngCategory as Range, rngBondTotal as Range, rngMaturityDate as Range, rngEndingMonthDateMaturity as Range) as Double

It would help to post a spreadsheet, disguise any confidential information beforehand.

3. ## Re: Better Way? (VBA/Excel/97)

I'm totally clueless about financial calculations, so forgive me if you've already tried this, but have you investigated the Pmt, IPmt, an Nper functions?

4. ## Re: Better Way? (VBA/Excel/97)

Give the following a go.
It replaces the named ranges with range objects.
objMaturity(i) is replaced by a variable that is only calculated once instead of possibly 3 times.
The calculation of Weight is also simplifed.
I have no way of testing it so you will have to let us know.

Jim Cone
San Francisco, CA
'------------------------------------------------

Function WeightBond()

Dim Weight As Double
Dim AvgMatBond As Double
Dim RemainDays As Double
Dim objMaturity As Range
Dim objCategory As Range
Dim objBook As Range
Dim i As Long
Dim dblMaturity As Double
Dim dblReciprocal As Double

Set objMaturity = Range("Maturity_Date")
Set objCategory = Range("Category")
Set objBook = Range("Book_Value")
dblReciprocal = 1 / BondTotal

For i = 1 To Range("Price").Count
dblMaturity = objMaturity(i).Value
If dblMaturity < EndingMonthDate Then
Weight = 0
'Calculate days for bonds
ElseIf objCategory(i).Value = "b" And _
dblMaturity > EndingMonthDate Then

RemainDays = dblMaturity - EndingMonthDate - 1
Weight = (objBook(i).Value * dblReciprocal) * RemainDays
AvgMatBond = AvgMatBond + Weight

End If
Next 'i
WeightBond = AvgMatBond

Set objMaturity = Nothing
Set objCategory = Nothing
Set objBook = Nothing
End Function

5. ## Re: Better Way? (VBA/Excel/97)

That did it. The run time went from three or so minutes to a few seconds. I'm thinking that the dblReciprocal was a significant improvement in that the BondTotal function was only being called once instead of "i" times. I'm slow, but hopefully I'm catching on. Thanks.

6. ## Re: Better Way? (VBA/Excel/97)

Unfortunately, I don't think that any of these functions would be helpful in what I'm trying to do. Thanks.

7. ## Re: Better Way? (VBA/Excel/97)

You are welcome and thanks for letting us know how it worked out.

Jim Cone
San Francisco, CA

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•