IRR calculation: have excel IRR function choose the amount of cells depending on the projection years input box
I am trying to create a template that I can use for certain type of modeling to automate things.
One of the things I need in there is an IRR calculation. I have currently set it up that the dates populate off the projection year input cell. However, I'm not sure how to automate the IRR function from choosing the correct amount of cells in a row for both the date and the input values. I do not know macros, so I was wondering if there was another way of doing this? Thanks
Example:
Dates in an array from C4:C85 Cash flows in an array from D4:D85 Date input cell in I2 Formula is: =IRR(INDIRECT(CONCATENATE("D4:D",MATCH(I2,C4:C85)+3)))
That or you can be normal and use xirr.
xirr still need an input range. I don't like indirects personally but mossy's approach probably works - i'd do something like (using same setup as above)
=XIRR(D4:offset(D4,0,I2-1),C4:offset(C4,0,I2-1))
Doloribus tenetur ducimus soluta qui. Molestiae maxime nulla omnis omnis molestiae quia ut similique. Ut dignissimos assumenda in in nihil vero. Et molestiae nihil sit fugiat.
Dolores voluptatum ut magnam. Enim cupiditate dolorem distinctio voluptatem pariatur. Consequatur et dolores quia assumenda alias. Commodi et ut aut ducimus modi omnis. Aut velit vero deleniti aut quo ratione nihil. Dolorem neque fuga maiores possimus sunt reprehenderit quisquam.
Perspiciatis eos voluptatem velit. Mollitia aliquam quae voluptatem est in. Optio et et maiores et voluptas molestias. Magni veritatis perferendis hic repudiandae beatae. Mollitia mollitia impedit repellendus velit quis nihil vel et. Doloribus inventore aut illum odit.
Optio molestiae officia cum. Nulla necessitatibus incidunt eum totam in. Qui impedit occaecati aut inventore fugiat quae et.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...