Excel question

gjrc

gjrc

gjrc

Soldato
Joined
18 Oct 2002
Posts
6,830
Location
London
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
 
Last edited:
Can't really make head nor tail of that spread sheet to be honest but is your formula not based on the number of years? If it is then instead of hardcoding it into the forumla just point that bit towards a cell containing the number of years.
 
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?
 
I know what your trying to do but i'm struggling to get an Excel solution. You will probbaly need some VB.

Essentally the range of cells you are looking at changes dependent on the years. So you are currently pointing to range b:bs for 5 years. However for 3 years or 12 quarters you would only want range b:m in your output formula.

So your formula range should be dynamic based on the value in cell b12. I'll have another look later when i'm less busy.
 
Last edited:
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.
 
Think the indirect function is wht you are after. You could run a table at the back, so when you put in 5 years for example it will return the specific range of cells you need to return. Do a vookup or index to return the range to a cell in the summary sheet, you can then use the indirect function. Pretty hard to explain how it works as I dont really understand your spreadsheet. But this solution may well be what your after.
 
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 :)
 
Last edited:
use Malakite's approach but with R1C1 references
You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.). In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

R[2]C[2] A relative reference to the cell two rows down and two columns to the right
R2C2 An absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) to the cell in the second row and in the second column
Click to expand...
 
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 :)
Click to expand...

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
 
Last edited:
Ye I'm not to up to speed on financial calculations so wasn't sure exactly what needed to be included. The reason it doesn't work past 26 quarters atm is we are using the Char function to specify the column number using the ANSI character set and obviously there are only 26 letters in the alphabet. so when it moves onto Character 27 it doesnt look for column AA.


As mentioned above using the R1C1 referencing would work better. I'll see if I can get some time to try that for you today.
 
Ok I have managed to get it working past 26 quarters. If you add this bit of code

Code: 
Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Into a new module, then insert this function into Cell D12 pointing to C12 (the quarters calculation). So =ConvertToLetter(C12)

This will give you the column that your quarter period ends at.

The forumla then becomes

=IRR(INDIRECT("B26:"&D12&"26"),0.01)

Now i'm having a problem getting the last column you specified into the formula. Never having used IRR before I assume these values need to be the last values in the range for the function to work?

I think what you may need is to use some code to copy the range you need into a seperate area, add the final column to here then have the formula point to that.

Hope thhis is clear.
 
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
 
Last edited:
Ok i've just been being slow. Dont need to move the columns, I was just forgetting extra parenthesis. So the formula should be

=IRR(INDIRECT(("B26:"&D12&"26"),BS26),0.01)

This will include the value in the final column. You just need to change this formula at the end so it calculates on the number of quarters in our range rather than the value in BS21.

If i'm understanding the sheet correctly this should do what your looking for. As said though i'm dont really know much about the financial calculations so let me know if there is anything else.
 
Last edited:
Malakite,

thanks again.

just tried the new formula its showing teh same result as the previous formula?
 
Last edited:
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
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom