looking for help writing a formula for both average and irr functions over a dynamic range, with the number of cells in the calculation being referenced as a number on a separate sheet. Can someone help me with a formula to accomplish this?
in the attached worksheet rows 27 and 30 are fine as is with a 10 year hold. i have on another sheet a cell for hold period that will change from 1-10. if i change the hold period to say 5 years then everything in columns k through o turn to zeros. problem then is that my irr and average cash on cash formulas in rows 27 and 30 are picking up the extra zeros and skewing the calculation. I want to dynamically adjust the references in those formulas to account for the actual holding period.
Bookmarks