• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

filling in the date

Lawrence429

New Member
Hello --
I am trying to build an XIRR workbook for different amounts of time. What I would like to do is enter a start date and a finish date and have a table created with all the dates inclusive between the two. So if I enter for example, 1 Sep '19 to 1 Dec '36 it would automatically create a column with 1 Sep '19 at the top and the first of each month up to and including 1 Dec '36 (if necessary I can cap the input to 30 years or something). I know I can grab the bottom right corner and drag it down but I am trying to make this as quick and easy as possible.
 
This is only any use to you if you are an Office 365 user and can switch the account to 'insider'.
The new function SEQUENCE will give all 6302 date values from
= SEQUENCE( 1+ endDate - startDate, , startDate )

Otherwise you are stuck with the usual nasty 'and fill down' strategies.
 
Thank you Peter. I will try it out tomorrow or maybe Saturday. So there is no VBA magic or some work around if my friend (that I am building this for) uses an older version?
Thanks again.
LCD
 
@Lawrence429
My initial thoughts are
1. A user-defined function (UDF) will not really help because, although it will calculate the array, to output it to the sheet requires the user to select the relevant range and commit the formula using Ctrl+Shift+Enter (CSE). That is as inconvenient as fill down.
2. There must be an Excel 'tips and tricks' merchant somewhere that will know a slick short-cut to selecting an inconveniently large range; it is just not my scene! If you can select the range Ctrl+Enter populates it.
3. A macro linked to a command button (or shape if you wish to pretty it up) could write the sequence to a range with a pre-defined starting cell. You could also call the macro automatically if the start or finish dates change (using the worksheet change event handler). This would give a list containing date values rather than a formula.
 
First put your starting date in a cell, then with that cell selected, in the Editing section of the Home tab of the ribbon there is a Fill dropdown:
61048
(I'm in the UK and have UK dates).
 
Thank you Peter, Vletm and P45cal :).
Peter -- I do not run 365 and do not know if my friend does so even though that is a cool tip, I can't use it :*(. I was going to work on building the macro, but
P45cal came up with an idea that will work for this application (nice job).
thanks again.
Lawrence
 
So I followed P45cal's advise and it does a great job at filling in the dates. However it messes up the IRR equation. I wrote the equation as =XIRR('Monthly Investments'!G2:G615,'Monthly Investments'!H2:H615,0.21) to accommodate up to 50 years of monthly payments; it works great if I manually fill in the date range -- disregarding any blanks. But when I use the fill function, I have to manually update the equation from G615 / H615 to the last cells populated. This is rather frustrating. Does anyone have any thoughts on how to work around this?
The first tab explains how to use this, the second is the data the user would enter, and the third has the date range. Is there a way the equation could be automatically updated?
thank you much
Lawrence
 

Attachments

  • IRR with Macros.xlsm
    231.4 KB · Views: 8
Lawrence429
You asked thoughts ...
1) You know the last date eg Cell D15
2) You can get that dates row from 'Monthly Investments' C-column eg with Match-function
3) You study how to use Indirect -function to change '615'-row to needed row '193'.
4) Do same with other options ( note columns )
 
If you insert a row with 'amount' and 'date' headers and use it as the header row of a one record table, the table will expand when you introduce the series. You can then reference to dates and amounts using structured references in place of direct referencing.

I also note that your file is macro-enabled. A macro would allow you to resize the table and populate it, using values or a formula, in response to changes on the overview worksheet. That would allow you to avoid the manual fill down step (though I did find it interesting since I have never thought to use that control).
 
it works great if I manually fill in the date range -- disregarding any blanks. But when I use the fill function, I have to manually update the equation from G615 / H615 to the last cells populated.
I tried manually filling in the date range but it didn't work at all; I still had to update the formula manually. I haven't been able to reproduce that behaviour.

Try this formula in cell D17:
Code:
=XIRR('Monthly Investments'!B2:INDEX('Monthly Investments'!B15:B615,DATEDIF(D14,D15,"m")),'Monthly Investments'!C2:INDEX('Monthly Investments'!C15:C615,DATEDIF(D14,D15,"m")),0.21)

To confirm that it's using the right ranges you can use the Evaluate formula icon in the Formula Auditing section of the Formulas tab of the ribbon, pressing the Evaluate button until you reach:
61087
 
Last edited:
Ok I figured out why it doesn't work -- the XIRR function is trying to calculate all the cell in the array and the way I have this built now, there is an "=IF(C15="","",Worksheet!$B$14)" equation in all the cells in b13-b615. The "unused" equations are getting wrapped up with the dollar amounts.
So unfortunately Vletm I am not following your suggestion (thank you much though), but I thought if I could have something like this in
cell D13:
"If c13>0, then insert value in b13", autofill this down to D615 and that way the only cells in column B that would be used would have a number and not a formula. Any thoughts on this? That way the XIRR formula can stay at b2:b615 and c2:c615.
 
Lawrence429 ... hmm?
Did You try to read my text?
You've asked Excel-formula-solution...
Check this files yellow range --- cells D19 ( step #2 ) and D20 ( step #3 ).
If to do this with VBA then it would be other case.
 

Attachments

  • IRR with Macros.xlsm
    234.4 KB · Views: 10
Thank you VERY much -- I was trying to figure this out for hours last night and I just wasn't seeing it. I was trying to use "INDIRECT" as a function and not having any luck connecting the dots. :cool: The joys of being new to Excel; always learning a better way.
 
Back
Top