Real estate IRR & NPV Question
Hi guys,
While studying for the modeling test, I got stuck on this question and can't sleep because of this. So I came here to seek for your help.
This might be an easy question for you guys, but help me out...
Year 1 2 3 4 5
Net Operating Income $100 $110 $120 $130 $140 + Sold for $1,000
Assuming that the purchase price can be financed with 70% leverage at a 6% interest rate for 5 years, how much would Investor be willing to pay if Investor wanted to achieve a 20% levered IRR?
Please also show the cumulative net profits that Investor would achieve based on this scenario and those profits as a multiple of the initial equity investment.
I guess you can do this by hand, but do you guys know how to figure this out in excel?
Again, sorry if this is an easy question.
Thank you.!
The easy way I did it was thru goal seek. Make your loan amount based on 70% of a random purchase price and your loan payments based off whatever that loan amount is (also don't forget the ballon pmt to be equal to Loan amt, assuming I/O). Then set up a CF after Debt Service column which is what you will run the IRR off of to be goal seeked to 20% off of that placeholder purchase price
$1,061
Prospie, how did you arrive at that result? I am showing $1,850 as the price.
I did exactly what kmzz described - granted, I did it fast so there might be a sloppy mistake. Feel free to PM me for this, but it's really easy, and anybody who wants to be an analyst should know how to do this.
I've been thinking about this over lunch. Can anyone come up with a way to accomplish this in excel without guess and check?
Its circular (you need to know the loan amount to come up with interest payments for LIRR but you need to know the interest payments to back into the loan amount). So I know there isnt an equation to figure this out but I wasn't sure if Excel has a function to do this? Afterall to calculate IRR excel just does a iterative guess and check.
If you enable iterative calculation in the options you can accomplish this without the need for goal seek. Calculate the NPV of years 1-5 NCF using 20% (goal IRR) as the discount rate. This will give you $318 in this example. Then use a formula that adds the NPV and the loan amount cell. This will create the circular reference (loan amount linked to 70% of price) but with iterative calculation enabled Excel will automatically recalculate as inputs are adjusted.
Did anyone get $1,141.70 or am I missing something?
Sounds like you have the initial purchase occurring in year 1 along with first cash flows, but it should be in year 0.
Thank you sir
Just did this and got $1,061, but I have a question: How did you guys determine to use straight line payments (equal interest pmts each year, plus balloon pmt to eliminate principal in yr 5) rather than mortgage style payments? Is this typically the norm for real estate assets?
I just went with the interest only assumption because the original question did not provide that information. The alternative would be an amortizing loan but again, an amortization term was not provided, and you would almost never see a 5 year amortized loan. I suppose one could justify it being a 5 year term with 30 year amortization, but given the relative simplicity of the question, the safe assumption is interest only.
As far as the norm, generally on short term financing of just a few years it will be interest only and longer term (7-10 yrs) will be amortizing over a 20-30 year period with balloon at expiration.
Thanks for clearing that up.
Do you think this is something they can ask in an real estate finance case study interview? Anyway thank for the basic but good question.
Debitis ut aut minus molestiae. Quidem omnis omnis odit. Necessitatibus ut sit laborum recusandae. Aut sunt nihil rem laborum optio dicta et omnis.
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...