Payback time calculation in Excel

  Timoj 11:30 04 Oct 03

I need to make simple model for investment calculation, where also payback time is shown. In one cell is the original investment (e.g. -100) and in the following cells income due to this investment. Annual income is not constant, but varies between the years. Calculation time can be 10-20 years.
I have only been able to make it simply by guessing a payback time (e.g. 5 years) and then using average function to calculate average income for the selected years, payback time is calculated by dividing the investment by thi average income. If the result differs from the guess, I have tio manually change the guess.
I would think that it was possible to make Excel to do this iteration automatically. It could e.g. count the number of cells participating in the average calculation and compare it to the resulting payback time (rounded up to next full year) and then add or substract one cell from the average calculation. The maximum number of cells in the average calculation should be same as the calculation time in years.

  VoG II 11:39 04 Oct 03

You could try using Tools/Goalseek or Tools/Scenarios

Type Goalseek or Scenarios into Help to see what they do.

Excel has a range of Financial functions that may help here - I'm not familiar with most of them. (Some of them need the Analysis Toolpack to be loaded.)

  Cesar 11:48 05 Oct 03

In all Financial calculations there are five main parameters, Period, Interest, Present Value, Future Value and Payment, One of those must be a negative value, (usually payment) it appears that you are looking for a FV scenario, but beware of using excel, see my Previous thread.
click here

This thread is now locked and can not be replied to.

Surface Studio vs iMac 5K comparison review: Apple and Microsoft all-in-one PCs battle it out

1995-2015: How technology has changed the world in 20 years

Social media image sizes, specs and aspect ratios: the ultimate guide

New MacBook Pro release date, price and specs | New MacBook Pro 2016 announced: Now with Touch Bar…