- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Formula Help
Posted on 3/23/18 at 3:03 pm
Posted on 3/23/18 at 3:03 pm
I humbly come to the MB to help get a quick fix for a formula. I’d typically take the time to think about how it should be pieced together, but in a bit of a deadline crunch and it’s late in the afternoon, so here goes:
Cells A5:A272 are month end dates in 1 month intervals
Cells B5:B272 contain project cash flows (there are multiple columns, but we’ll use B to keep this simple
Cell B3 has the month end date of the project inception
Cell B4 has either the disposition of the project date or the last month end date
I need to write an XIRR formula that will measure XIRR from inception to disposition out of the range of dates I have in column A.
I’m imagining some sort of Index, match buried in there, but I haven’t figured out the cleanest way to write it.
Cells A5:A272 are month end dates in 1 month intervals
Cells B5:B272 contain project cash flows (there are multiple columns, but we’ll use B to keep this simple
Cell B3 has the month end date of the project inception
Cell B4 has either the disposition of the project date or the last month end date
I need to write an XIRR formula that will measure XIRR from inception to disposition out of the range of dates I have in column A.
I’m imagining some sort of Index, match buried in there, but I haven’t figured out the cleanest way to write it.
3
Posted on 3/23/18 at 3:58 pm to southernelite
Get an intern and make him her do it manually.
Posted on 3/23/18 at 4:43 pm to deeprig9
I’ve been lobbying for an intern but they won’t give me one. 
ETA: Also, who the frick downvotes all of my money talk posts, that’s some dedication
ETA: Also, who the frick downvotes all of my money talk posts, that’s some dedication
This post was edited on 3/23/18 at 4:47 pm
Posted on 3/23/18 at 5:25 pm to southernelite
If I understand you correctly, you want a formula driven XIRR result that adjusts based on what disposition date you set?
If you were really lazy, you could just add a column "c" with something along the lines conceptually
IF(A5= Target Date, XIRR Formula up to target cell, 0)
Basically, calculate XIRR for the date you want otherwise = 0
Just sum the whole column "C" which should only include the XIRR at the target date you set
If you were really lazy, you could just add a column "c" with something along the lines conceptually
IF(A5= Target Date, XIRR Formula up to target cell, 0)
Basically, calculate XIRR for the date you want otherwise = 0
Just sum the whole column "C" which should only include the XIRR at the target date you set
This post was edited on 3/23/18 at 5:26 pm
Posted on 3/23/18 at 5:35 pm to GenesChin
I’m not sure that would work, and even if it did, I have 50 columns of projects, so that’s not ideal.
It needs to measure from a start date to an end date. All the projects have different start and end dates.
I’ll work on the formula over the weekend.....based on my research, I need some combo of address/index and possibly an offset dynamic range.
It needs to measure from a start date to an end date. All the projects have different start and end dates.
I’ll work on the formula over the weekend.....based on my research, I need some combo of address/index and possibly an offset dynamic range.
This post was edited on 3/23/18 at 5:37 pm
Posted on 3/23/18 at 7:56 pm to southernelite
Misunderstood what you were looking out
Posted on 3/23/18 at 9:28 pm to southernelite
Use INDIRECT and ADDRESS to set the starting and ending cells. Should be pretty easy once you get the variables linked up right. You can drag it across.
Posted on 3/24/18 at 1:38 am to southernelite
I’ll experiment with this tomorrow and see if I can work up a solution.
Posted on 3/24/18 at 9:38 am to lynxcat
Ended up going with this, and it works....
Row 1 - Project Name
Row 2 - Project Code
Row 3 - Inception Date
Row 4 - Disposal Date
Row 5 - XIRR formula
Row 6-273 Dates through 2028; Cash flows
=XIRR(INDIRECT(ADDRESS(MATCH(C3,$A$6:$A$273,0)+5,COLUMN(C1),1,1),TRUE):INDIRECT(ADDRESS(MATCH(C4,$A$6:$A$273,0)+5,COLUMN(C1),1,1),TRUE),INDIRECT(ADDRESS(MATCH(C3,$A$6:$A$273,0)+5,1,1,1),TRUE):INDIRECT(ADDRESS(MATCH(C4,$A$6:$A$273,0)+5,1,1,1),TRUE))
Row 1 - Project Name
Row 2 - Project Code
Row 3 - Inception Date
Row 4 - Disposal Date
Row 5 - XIRR formula
Row 6-273 Dates through 2028; Cash flows
=XIRR(INDIRECT(ADDRESS(MATCH(C3,$A$6:$A$273,0)+5,COLUMN(C1),1,1),TRUE):INDIRECT(ADDRESS(MATCH(C4,$A$6:$A$273,0)+5,COLUMN(C1),1,1),TRUE),INDIRECT(ADDRESS(MATCH(C3,$A$6:$A$273,0)+5,1,1,1),TRUE):INDIRECT(ADDRESS(MATCH(C4,$A$6:$A$273,0)+5,1,1,1),TRUE))
This post was edited on 3/24/18 at 9:46 am
Posted on 3/24/18 at 4:33 pm to southernelite
Impressive formula
Posted on 3/24/18 at 7:12 pm to lynxcat
Thanks
It feels pretty damn good when you bang out a formula like that, hit enter, and you get the result you wanted.
It feels pretty damn good when you bang out a formula like that, hit enter, and you get the result you wanted.
Posted on 3/25/18 at 1:25 am to southernelite
Definitely misunderstood what your Excel book looked like
You could have mapped using vlookups as well if you wanted to go down that route
You could have mapped using vlookups as well if you wanted to go down that route
Posted on 3/25/18 at 5:31 am to GenesChin
Vlookups would have returned values, not references.
I think the way I did it was the path of least resistance.
I think the way I did it was the path of least resistance.
Posted on 3/25/18 at 10:48 am to southernelite
Misunderstood, I'm saying create vlookup tables, not vlookup on the data you have
Your method looks better to me
Your method looks better to me
Posted on 3/25/18 at 11:02 am to southernelite
quote:
southernelite
quote:
ETA: Also, who the frick downvotes all of my money talk posts, that’s some dedication
TDings overcome with bots
You write a badass formula featuring functions that 99.9% of users have never used (INDIRECT and ADDRESS, specifically) and you still get downvoted.
Posted on 3/25/18 at 11:06 am to southernelite
impressive formulaic syntax SE
Posted on 3/25/18 at 11:14 am to lynxcat
I guess maybe I should have asked for help compiling crypto returns and I may not have gotten downvoted.
Posted on 3/25/18 at 6:34 pm to southernelite
Although Crypto’s have overtaken the board (not so much recently), that formula looks complex as hell.
I haven’t sat down and looked at what you’re trying to do, but it seems complicated and ripe for errors.
for figuring it out though.
I haven’t sat down and looked at what you’re trying to do, but it seems complicated and ripe for errors.
for figuring it out though.Posted on 3/25/18 at 7:00 pm to LSUtigerME
Appreciate the props from everyone.
Also, yeah, I think the crypto guys have their own channel elsewhere for the most part.
Also, yeah, I think the crypto guys have their own channel elsewhere for the most part.
Posted on 3/26/18 at 12:26 pm to southernelite
Formula does indeed work
Recently I have preferred to reduce the complexity of the formula if they get to large, especially if others have to look into it, or if I need to troubleshoot heavy models
In this case I did a concat of the dates and values in two rows above the XIRR calc (row 5 & 6)
=CONCAT(ADDRESS(MATCH(B$3,$A:$A,0),COLUMN($A:$A)),":",ADDRESS(MATCH(B$4,$A:$A,0),COLUMN($A:$A)))
result - $A$8:$A$247 (same thing for values just one column over)
Then your XIRR calc is just two indirects =XIRR(INDIRECT(B$6),INDIRECT(B$5))
Recently I have preferred to reduce the complexity of the formula if they get to large, especially if others have to look into it, or if I need to troubleshoot heavy models
In this case I did a concat of the dates and values in two rows above the XIRR calc (row 5 & 6)
=CONCAT(ADDRESS(MATCH(B$3,$A:$A,0),COLUMN($A:$A)),":",ADDRESS(MATCH(B$4,$A:$A,0),COLUMN($A:$A)))
result - $A$8:$A$247 (same thing for values just one column over)
Then your XIRR calc is just two indirects =XIRR(INDIRECT(B$6),INDIRECT(B$5))
Page 1 of 2
Popular
Back to top
Follow TigerDroppings for LSU Football News