0

I hoping you able to help me. I am trying to calculate the Maximum Allowable Offer (MAO) which is=

[ARV] - [the preferred profit margin (I would want the ability to change this number)] - [the selling costs] - [the renovations costs] - [the wholesale fee] - [the holding costs] The issue that I'm having is that the holding costs is calculated based on the MAO value and I don't know how to calculate the holding costs while I'm trying to figure out the MAO.

Take a look please,

Thanks a lot: https://www.dropbox.com/s/dcfaqcx61uzeg3s/Flip%20Calculator.xlsx?dl=0 enter image description here

Saher

8
  • 4
    Questions should be self containing. Please include formulas and screenshots instead of link to a file. Aug 13, 2022 at 9:14
  • Also include your research - what you have tried so far & where it failed.
    – Tetsujin
    Aug 13, 2022 at 9:23
  • I searched a lot for a solution on YouTube and there is an extensive explanation but I didn't understand it well, in fact is I'm stuck with the idea itself.
    – Saher Naji
    Aug 13, 2022 at 10:52
  • What's the formula for holding cost? Aug 13, 2022 at 11:06
  • =SUM(C21:C26) I updated the last screen-shot to show all the formulas
    – Saher Naji
    Aug 13, 2022 at 11:08

2 Answers 2

1

Use some algebra to restructure the calculation. AND whatever degree of cleverness is needed to characterize the way the holding costs relate to the maximum allowable offer.

Let:

MAO = Maximum Allowable Offer
ARV = Estimated Sale Price
PPM = Preferred Profit Margin
SC = Selling Costs
RC = Renovation Costs
WF = Wholesale Fee
HC = Holding Costs

Algebraicly, you are doing:

MAO = ARV - PPM - SC - RC - WF - HC

but HC can be characterized in some manner as related to MAO. That might be a VERY complicated way, or a middling complicated way, or a simple percentage of MAO. For the purpose here, it doesn't matter: if you can't actually figure out how to characterize it you can't calculate it anyway and so have a much bigger and more basic problem. So presumably you can, and for ease here, I'll consider it to be a percentage (however that might be derived, simply or less simply). So HC can be said to equal X*MAO and your above equation becomes:

MAO = ARV - PPM - SC - RC - WF - X*MAO

Move that term to the left side of the equation ("collecting terms" in mathspeak) by adding it to each side. You can then factor it into two factors:

MAO * (1 + X) = ARV - PPM - SC - RC - WF

Now divide each side by that (1 + X) factor and you are done, algebraicly, since you have your single unknown component on the left and the known components on the right:

MAO = (ARV - PPM - SC - RC - WF) / (1 + X)

Now you just arrange it in Excel terms.

No matter how HC depends upon the MAO, it will be possible to characterize it. Formula, or formulas, plural, or table even if it has to be some value for every single penny rise or fall... it CAN be characterized. A combination. However. It will be possible because, again, if you CAN'T, you can't do this anyway. It may be the big bit of work here, more so as its complexity increases, but it can be done.

In my experience (my jobs over the years) algebraic steps have seldom been needed to solve my problems. Others may experience it to be useful kind of often. And in between. Surely some kinds of problems needing solved are always candidates. This one may be solvable otherwise, but why? Unless you really do have to type out a table penny by penny for offers up to millions of dollars (hundreds of millions of lines), even a jerking about table is just monkey work. And one can use tables to cover part of the range with formulas for the gaps between their regions in the possible values for the MAO. Whatever works best, including using it in the future.

1
  • Thank you very much
    – Saher Naji
    Sep 14, 2022 at 11:59
1

You’re not having an excel formula problem, you are having a problem with the way you have structured the analysis. You are trying to solve a math problem that has an endemic circular reference by looking for some trick in excel to solve the circular reference. The reason that excel can’t allow circular references is because the underlying math can’t allow circular references. Yes, as others have mentioned, you can try a goal-seeking spreadsheet but that’s unduly complicated.

I think you need to restructure the very essence of your analysis thus:

  • Future market value of house = A (example 250,000)
  • Cost of renovation to get to that value = B (example 80,000)
  • Profit I need to make the deal worth while = C (examples 15%, or $20,000)

In this example that means my acquisition cost PLUS my holding costs must be less than $150,000 in order to leave me profit C.

In other words, my question is, how big an offer can I make such that the offer + holding costs = 150,000?

So now I have a non-circular problem to solve. Given an interest rate of X and an estimated months Y for the combined duration of renovation + listing, I can solve for a maximum offer. This is a simple present value vs future value calculation given X% and Y periods.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .