I'm working on a spreadsheet that I'll send to customers who are interested in switching to Tivo from their current crap cable boxes. Much of the dollar amounts are known, like the current cost of a Tivo Bolt, Tivo Mini, yearly Tivo subscription, and even the monthly cost of a cable card.

What I don't know is how much the customer is currently spending for their cable boxes, and how many they have. I've made a spreadsheet that has only two boxes for the customers to enter that information. It then decides how many Tivo Minis they'll need, and gives them a total for the initial cost as well as the fixed yearly cost of Tivo Service and a CableCard (currently $210 on Fios, for example).

The formula I'm trying to create is sort of a "break even" point formula. I want to see how long (in months) it will take for the customer to start saving money on this investment. I need to factor in the initial investment plus the ongoing costs.

Does anyone know how I make a formula that can do this?

Without seeing the spreadsheet, I can't tell you the exact formula to use.

However, under 'Data' there is a 'What-If Analysis' menu. Selecting 'Goal Seek' lets to choose which cell is the target cell, what the value you want of that cell to be, and which cell to change to reach that goal.

So by designing the sheet to figure out the list of initial costs and monthly costs, then have the monthly costs multiplied by a 'month' cell, you can set the spreadsheet up in such a way that Goal Seek will change the number of months until the difference in price is $0.

Once you get that part working, you can record the actions as a macro and have a command button to run it so the client doesn't have to do any manual operations at all.

Edit: See my attached example. I have 4 columns, one 'Recurring Costs' and one 'Non-Recurring' for both the current and proposed setups. Plug the costs in. Sum the columns. Subtract the proposed non-recurring from the current non-recurring to create the non-recurring difference. Subtract the proposed recurring from the current recurring to create the monthly difference. Create a cell for the number of months required to recover costs and put a fake value in there. Create a cell for difference in price using the formula 'Non-Recurring Difference + (months * Monthly Difference)'.

To do the optimization go Data -> What-If Analysis -> Goal Seek and the Set cell is the Difference in price (B14), To value is 0, By changing cell is the months (B12).

Isn't it just Months = NewSunkCosts / (CurrRecCosts - NewRecCost)
Then round up.

Far too easy doing it that way. Where is the fun in that?

Damnit, I knew I wasn't looking at it correctly. Thanks for that, Glenn. I've finished the spreadsheet.