Results 1 to 3 of 3

Thread: Issues getting a vba function to call another vba function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Issues getting a vba function to call another vba function

    I'm currently a college student in a financial modeling course and we've recently jumped into vba coding. I don't have much experience, so everything is a bit new. We have a question that assigned to us that follows:

    A hybrid linked note is an instrument that provides investors fixed income and equity market. It is structured by combining a return that is 75% equity and 25% fixed income. The investment pays the greater of the par value, $1,000, or the price times the 75% percentage increase in the S&P 500 plus 25% the return on the Barclays US Aggregate Index.
    Write 2 functions (hlnPayoff, hlnReturn). The first, hlnPayoff, calculates the security payoff at maturity. The second, hlnReturn, calculates the annualized return assuming continuous compounding. The function hlnReturn should call the payoff function.
    Price | Years | SP5000 | SP5001 | Barclay US Agg0 | Barclay US Agg1

    953 | 1 | 1783 | 1995 | 1810 | 1887
    914 | 2 | 1298 | 1498 | 1909 | 1942
    811 | 5 | 1288 | 1568 | 1710 | 1887
    733 | 8 | 1335 | 1270 | 1612 | 1923

    My code so far is as follow:

    Code:
    Function hlnPayoff(price As Currency, sp0 As Currency, sp1 As Currency, bar0 As Currency, bar1 As Currency)
    Const par As Currency = 1000
    
    
    hlnPayoff = price * (1 + (((sp1 - sp0) / sp0) * 0.75) + (((bar1 - bar0) / bar0) * 0.25))
    
    
    If hlnPayoff < par Then
        hlnPayoff = par
    Else
        hlnPayoff = hlnPayoff
    End If
        
    End Function
    
    
    Function hlnReturn(price As Currency, years As Integer, sp0 As Currency, sp1 As Currency, bar0 As Currency, bar1 As Currency)
    
    
    hlnReturn = Application.WorksheetFunction.Ln(hlnPayoff / price) / years
    
    
    End Function
    The hlnPayoff seems to be working fine, and I'm getting a result that I am satisfied with. But I'm having issues getting the hlnReturn function to work at all. I think my biggest issue is that the hlnReturn needs to call the hlnPayoff funtion, and I'm not entirely sure how to make that work. My issue seems to be that I'm getting an "argument not optional" error, and I'm not sure how to correct it.

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,645
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Issues getting a vba function to call another vba function

    You need to pass arguments when you call hlnPayoff.

    The arguments of hlnReturn are scoped only to that procedure, if you want them to be used for the hlnPayoff calculation, you have to pass them when calling the function

    Also the VBA function Log is the same as the worksheet function LN (different than the worksheet function LOG)

    Code:
    hlnReturn = Log(hlnPayoff(price, sp0, sp1,bar0, bar1) / price) / years

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issues getting a vba function to call another vba function

    Ah, thank you. That worked perfectly. As a follow up, do you think there is a better way for me to define my variables? I tried to Dim them, but then I kept getting a duplication error, so I went with the current method just to get it to work. Any suggestions? Or should I just leave it as is?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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