Reverse calculation

**JimDandy** · 08-27-2011, 01:39 AM

I’m in need of a formula that requires taking the result of a series calculations, and by then modifying the result, tell me what one of the starting values is. In simple terms…2 x 3 = 6, but what if I wanted to change the 6 to a 7 and find out what the number 3 becomes, assuming the 2 was a constant. That would require that the variable number 3 would need to change to 3.5. It’s this sort of challenge I have, but in more complicated terms.

The calculation I’m after takes a value calculated from a given set of inputs, one of which is Price, to arrive at a percentage called Cash-on-Cash. I need to be able to reverse the calculations so that by modifying the Cash-on-Cash I can arrive at a new Price. Some of the inputs will change and some will remain constant. Many of the variables change in relation to each other as most of them are portions of loan costs.

The inputs are:

NOI (Constant)
Annual Debt Service (Variable – This is annual loan payments)
Total Invested (Variable – This is a combination of the next 4 items)
Down Payment (Variable – % of Price)
Points (Variable – % of Price)
Closing Costs (Variable - % of Price)
Other Costs (Constant)
Interest Rate (Constant)
Term (Constant)

Cash-on-Cash (Variable)
Price (Variable)

If anyone can assist me in this I’d be happy to provide the workbook where the forward calculations are present. It’s the revers calculation that I’m struggling with.

**abousetta** · 08-27-2011, 02:02 AM

Hi,

I think a workbook is a must in this case because there are so many variables and its important to see how they all play together.

Question though, are you intending on changing multiple variables or is it just one variable being changed?

abousetta

**DonkeyOte** · 08-27-2011, 02:44 AM

@JimDandy,

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web.

You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.
We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post(s)

Read this to understand why we ask you to do this

**JimDandy** · 08-27-2011, 02:25 PM

I wasn't aware that the two forums watched each other. I prefer to only use one and have used this one for years, but I have found that recently I get far better coverage using both in situations where the solution is as complicated as this one is. If that's a violation it wasn't intentional.

Either way, I'm still interested in getting to a solution and welcome and appreciate any and all solutions, regardless of where it comes from.

The worksheet is attached. The area that I'm trying to work out is on the What If? page. All the variables are collected and calculate on the other pages. The darker highlighted cells are meant to be calculations and the lighter ones can be modified. I did not lock the worksheet yet.

The Cash on Cash spin button should cause the Maximum Offer field to change such that the financial calculations all result in the new Cash on Cash value the Spin Button sets. I'd like the CoC value to increment/decrement by 1% with each iteration. The values in the workbook as attached show a CoC of 16.8% and changing it to 17.8% should set the Maximum value to something real close to $16,595,397.47

This is cross posted: http://www.mrexcel.com/forum/showthr...76#post2843376