Two way Table issues

[rmatenchuk]

Hi,

I am trying to complete two way tables using a complicated XIRR calc as my formula. The table fills, but only with the original IRR value. Therefore, the whole table returns the same result. What am I doing wrong?

Thanks for your help!

Ryan

 

[Andrew Poulsom]

What am I doing wrong?

Welcome to the Board.

We have no idea unless you tell us exactly what you are doing.

 

[rmatenchuk]

Trying to create a property return schedule through an IRR sensitivity chart giving the returns with specific yields and rent levels.

The IRR is built into a cashflow on the page, which sums the rent, interest payments, etc. to arrive at a single line cashflow. I am trying to build a table that provides various returns for the cashflow given various possible exit values (rent level and Exit yield).


Does this help?

R

 

[rmatenchuk]

Sensitivity Tables
Rent (£ psf) vs. Exit NIY

Interval Across 3.5
Interval Down 0.25%


Return Rent (£ psf)
13.7% 49.5 53 56.5 60 63.5 67 70.5 74
4.00% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
4.25% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
4.50% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
4.75% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
5.00% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
5.25% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
5.50% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
5.75% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
6.00% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
6.25% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
6.50% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
6.75% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%
7.00% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7% 13.7%

 

[rmatenchuk]

In the above table the IRR of the base case is 13.7%, but as the variables are changed the IRR remains at 13.7%.

 

[Andrew Poulsom]

What formulas do you have in that table and where?

 

[rmatenchuk]

Andrew,

Thanks for your help. I realised that the F9 key works wonders in MS 2010.

All it needed was a Calculation reset. I feel like the guy when asked by a TV repairman over the phone, "is the TV plugged in?", saying "actually no".

Frustrated for hours!

Ryan