XIRR Problem

[dmw]

Hi,

I am wondering if it is possible to run XIRR until it equals 0% and then return the date on which IRR is 0%? I've been searching for an answer, but no luck yet. This is probably more complicated than I think. As always, any help is greatly appreciated. I have attached the spreadsheet I am working on.

Final_IRR_NPV_Bond_Calc_Buttons.xlsm

[Paul_Hossler]

1. You can clean up a lot and make it faster by not using .Select

Sub TodayDate()
    Range("A10").FormulaR1C1 = "=TODAY()+3"
End Sub

2. Using Option Explicit at the beginning of each module is usually a good idea

Option ExplicitSub First()
Call Headers
Call TodayDate
Call Cashflowschedule
Call PauseTemplate
End Sub

3. I had to fake the Bloomsburg functions - BDP and BDS -- to return hard coded numbers

4. These numbers are not right, but I used XIRR in the worksheet (orange) just to see the trend. The green lines are bracketing a zero crossing, but I doubt it would ever be exactly 0.000000.

The Excel Goal Seek might work, but a user function to take the same ranges and seek for 0.00000 +/- a small number or a zero crossing (is there be such a thing) would be possible probably

5. Can you post a WB w/o the bloomsburg functions and one that has the answer?

[dmw]

Paul,

thank you for your help and suggestions, I learn new skills everyday. As you can probably tell from my code, I am a beginner and am learning day-by-day. I'll play around with your approach and will report back.

[dmw]

Sub IRR()


' Autofill IRR from Cell F10


    Range("F10").Select
    ActiveCell.FormulaR1C1 = "=XIRR(R10C2:RC[-4],R10C1,RC[-5])"
    Range("F10").Select
    Selection.AutoFill Destination:=Range("F10", Range("A10").End(xlDown))


End Sub

What I am trying to replicate is the automatic IRR calculation in the right coulumn, in my case starting in cell "F10". I can't get it to stop where column A and B stop, these are dynamic ranges and vary from security to security. Any ideas?

In addition, I'll add a GoalSeek function as soon as I have figured out this IRR calculation. Thank you for your help!

[dmw]

Sub IRR()


' Autofill IRR from Cell F10
    Dim RC As Long
    RC = Range("A" & Rows.Count).End(xlUp).Row
    Range("F10:F" & RC).FormulaR1C1 = "=XIRR(R10C2:RC[-4],R10C1,RC[-5])"
    Range("F10:F" & RC).Select
    Selection.NumberFormat = "0.00%"


End Sub

I found a solution. Now I am getting an error in F10= #N/A and F11 down = #NUM! It's not showing the values...

[dmw]

I think I found the solution. Sorry for all these posts. Thank you for your help.

[Paul_Hossler]

I am wondering if it is possible to run XIRR until it equals 0% and then return the date on which IRR is 0%?

Does that find the date for 0%?

[dmw]

No, it doesn't find the exact date. However, by calculating each individual IRR, I can see what CF will be close to 0% and look at that coupon's payment date. I have a range, rather than an exact date, which is ok.