Excel question

gjrc · 14 Sep 2009 at 11:48

hi guys,

dont know if anyone can help here.

i have a spread sheet which is basically a cash flow for a property.

in this cash flow i work out IRR and exit values after holding the property for say 5 years (20 quarters)

i want to insert a function whereby i can change the hold period field to say 3 years and the IRR and exit value will also change accordingly.

i am happy to post the spreadsheet if required.

can anyone help with this?

thanks in advance.

/edit

here is the SS

http://www.zen10595.zen.co.uk/ss_hold_period.xls

gjrc · 14 Sep 2009 at 14:17

thanks for replying

the formual needs to be based off the hold period field.

so if i change that field i get an IRR based of exit value and rent at that time.

so it would have to select the fields at that date and calculate IRR and exit value at the required date.

does that make sense?

gjrc · 14 Sep 2009 at 15:18

thats the one yeah thanks! i need to calculate up to the date / period specified in hold period.

i would need to calculate IRR and exit value.

thanks a lot, would be a huge help if i could get this working.

the date values are actually dates its just the number of days since 1901 or something.

if you click the cell and press ctrl # then the date shows.

gjrc · 15 Sep 2009 at 10:27

Malakite said:
ok just had a quick look.

If you calculate your quarters in cell c12 (so b12*4)

Then use forumla

=IRR(INDIRECT("B26:"&CHAR(65+C12+1)&"26"),0.01)

to calculate your Quarter IRR should work. This just wont work past 26 quarters atm i'll try to fix that later tho

Thats the cleaner way to do it. However you could also add a letter row under your quarter row. e,g, so 1 has A ina row under it. Then use Hlookup to cell C12 rather than the Char part of the formula.

See if that works.

edit: Just seen dazzerd's response and seems to think the same which is a good sign

thanks so much for your help. it works a dream as i explained it!

however there is one problem.... and its my fault for not explaining. if you look at the last column of the sheet there are some extra values which would need to be calculated at the end of the hold period. these values then need to be included in the IRR calculation. so the formula in BS25 and the value in BS27 need to be included in the calculation. you are right that past 26 quarters it doesnt seem to work...????

i suspect that may be too many layers of complexity?

thanks a lot for your help so for.

g

gjrc · 15 Sep 2009 at 12:16

thank you!!

gjrc · 15 Sep 2009 at 16:34

Malakite,

cant thank you enough for this. it works perfectly.

just need to sort out getting those two figures into where i need them i relation to the hold period...

is there a way i can just insert those forumlas for those two cells?

the other formulas remian the same its just BS 27 and 25 that need to be moved / inserted for the calculation to work. also the value in row 24 needs to be removed in teh final calculation.

thanks so much again.

g

gjrc · 16 Sep 2009 at 11:01

Malakite,

thanks again.

just tried the new formula its showing teh same result as the previous formula?

gjrc · 16 Sep 2009 at 14:01

you are a genius!!

just need to work out why i am getting slightly different IRR calculations.

i suspect i am missing just one field. should be able to work it out.

thanks Malakite.

one other quick question..... sorry about this... any way i can start counting from column E for example rather than column A so E becomes one and so on?

no worries if not.

thanks again

g