Goal seek by formula

[matttan]

I have a spreadsheet that has some fairly complex formula-based steps. The purpose of the spreadsheet is to compare the end result of a lease where the monthly payment is calculated in two different ways - one via a PV-style calc, and the other using a pre-determined rate factor (which simply multiplies the original principal by a % to get an indicative monthly payment). The comparison is on the overall profit at the end of the lease, which is also PV'ed.

Now that I've confused you completely, this is what I'm trying to do...

The PV-style calc also includes the rate factor in its final profit determination - the importance of this is that it is used in both calculations. I am trying to set up a cell in the final comparison that essentially says 'here is the difference with your rate factor...you need to use this other rate factor to get zero difference'. Essentially, I am trying to put together a goal seek via formula. I use goal seek frequently, and have already used it in trying to work out an automated formula. However, if I can, I really want to have an automatically calculating cell that will provide the same information as the goalseek.

Does anyone know if this is possible? Because the overall calcs are quite long, I can't really explain them all fully, though if someone thinks they might have a solution if they see it in more detail, I'd be happy to email them the file to have a look at.

Thanks for any advice!

 

[CraigM]

It sound like you might want to have a look at the IRR function. This will give you the rate required for a stream of cashflows to be equal to another amount.

 

[Chitosunday]

Mattan

It's best to elaborate your problem in this post so everybody can help. If nobody can give the correct answer you can email it to me.

 

[matttan]

I'll try and elaborate...

One of our staff has a guide that helps him calculate the monthly payments for a given lease - a matrix, based on asset value and length of contract. It gives him a % (calc it the rate factor), which he multiplies by the asset cost, to get a monthly payment. This has been predetermined to be within the range that we're looking for. We want to give him a tool so that he can compare the price he quotes to the actual profit on a PV basis.

Now to further confuse...this is for variations on original leases. That is, a customer might call us and say they want to upgrade their computer. We calculate our estimated profit, and also an estimated profit for if the two leases were simply taken out separately, rather than the second varying the first. The aim is for the variation to at lease result in the profit of the two side by side leases, and this is where I'm looking for a 'zero' percent.

So, I'm trying to get a formula that will tell me what rate factor to use to get a break even, if the profit difference is negative. Like I said, goal seek does it no fuss, but I'm not sure what I can do to ensure it is dynamic.

Thanks in advance for any help.