Solving for Purchase price given IRR

Apologies in advance if I'm not supposed to share outside links. Are you trying to calculate a going-in residual land value? If not, I think I know what you're trying to do and this link might help, with using a simple solver function

No I’m trying to calculate the value drop of buildings based on increased interest rates really.

For this exercise, I want to show what purchase price we would want for x IRR

=NPV(Desired IRR, cash flow stream)

Goal seek would work but NPV might be more automated if that's what you were looking for. Just link the rate in the NPV formula to your IRR cell.

OP here - thank you all for the responses, I really appreciate it. I guess the thing I am grappling with is the equity inflow & outflow. This really isn't possible is it since I'm looking for a negative outflow of X which is also the purchase price.

This should be pretty simple. The IRR is the same thing as the discount rate.

If the investment is unlevered (no debt), then you would discount all of the projected cash flows to time zero using your IRR input and sum them together, which would give you a present value. If you want this number to appear negative, you would simply add a hyphen in front of the sum formula “-sum()”. Note that you would then need to add back acquisition closing costs to get to your purchase price. Unlevered discount rates (unlevered IRRs) are the customary method of valuing any asset.

If the investment is levered (includes debt) and you want to use a levered IRR input (not recommended), then you need to discount the net cash flow to equity after debt service and sum the cash flows. This will give you equity value, so you would need to add back the loan proceeds, loan closing costs, and acquisition closing costs to get to the purchase price.

Read Investopedia’s article titles “How to Calculate a Discount Rate in Excel” if this is not making sense to you, or Google how to discount cash flows. You can use the NPV formula from Excel, but I think it’s cleaner/better to use real math:

Present Value = Future Value / ((1 + discount rate)^(year))

Not to be rude or anything but isn't this literally the first thing you learn in finance?

I have a macro that uses goal seek to adjust Px to a YoC. IRR would be the same. Here's the code that I assigned to a button (with named ranges):

------------------

Sub GoalSeek()

Dim intSeek As Integer

Range("YieldOnCost").GoalSeek Goal:=Range("SeekTarget"), changingCell:=Range("PurchasePrice")

MsgBox ("Purchase Price updated successfully")

End Sub

------------------

YieldOnCost = the result cell with your formula for YoC (can be IRR)

SeekTarget = an input cell with what I want to goal seek YoC to

PurchasePrice = the input that I'm changing

Might not be the approach you are looking for, but it worked well for me. Feel free to reach out if you have any questions about it. 

You XNPV the cash flows and you back into the equity check. This plus debt raised equals total sources. Sources equal uses. Total Uses less fees and min cash equals purchase price (EV).

There are a lot of overcomplicated answers in this thread. You don't need some convoluted VBA script, just a solid understanding of the most basic functions in finance.

XNPV represents the purchase price at which you are indifferent to buying or not buying a given stream of cash flows, given an assumed discount rate (in this case your IRR). You will see that if you discount properly to solve for a purchase price at your given rate and then re-run XIRR on the CF stream that begins with the negative of purchase price followed by the asset's CF stream, you will get your desired IRR.

not sure everyone gave this a real answer or  maybe i am not following, say you have a purchase price, 3 years of cash flows, and a target IRR, what is the excel formula to use in the sale year 4 that calculates a sale price targeting "12%" IRR. "what is the purchase price necessary to achieve a 12% IRR given the purchase price and 3 years of cash flow?"

NPV..

I just solved for a similar scenario a couple of weeks ago using a data table in excel.
 

Using the DCF approach I used a data table in excel to generate a matrix of returns based on the desired Modified Internal rate of Return (can also use IRR), a range of purchase prices and a range of rental incomes.

Using the data table I can explore the swing in purchase and rental price and its impact on meeting the desired return.

Highly recommend checking out Josh Karr's playlist on data tables and in particular the tutorial titled "Data Table Part 3- Issues" on youtube to ensure you are using the data table correctly. It's a bit particular in terms of how you set it up.

No one has answered the question. in Excel the following cash flow streams:

-$1,000  |  $54  |  $95  |  $103  |  $81  |  $___________

Desired IRR: 15%

What is the formula to go in the $_____ Cell to calculate a sale price that will result in a cash flow stream with a 15% IRR.