Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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
  •