Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to get number + 80 % from Previous Balance
I have thefollowing
Y1 Y2 Y3 Y4 1000 1000 1000 1000 800 800 800 640 640 512 Total 1000 1800 2440 2952 the logic like this Every year 1000 New Plus 80 % from previous year diminishing value so Y3 figures are like this 1000 New 80 % of Y2 New =800 80 % of Y1 remaining (800)=640 Can I get this one through a formula thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to get number + 80 % from Previous Balance
Hi Abdul
There may be easier ways, but this seems to work. I put the number of Years in C1, the Principal (1000) in D1 and the Percentage (80%) in E1, and then =IF(C1=0,D1,D1+SUMPRODUCT((D1*E1^ROW(INDIRECT("1:" &C1))))) With 0 in C1 1000, 1 in C1 1800, 2 in C1 2440 etc. Obviously you could hard code the values into the Sumproduct formula if you wish, as opposed to using cells to hold the variables -- Regards Roger Govier "Abdul" wrote in message ... I have thefollowing Y1 Y2 Y3 Y4 1000 1000 1000 1000 800 800 800 640 640 512 Total 1000 1800 2440 2952 the logic like this Every year 1000 New Plus 80 % from previous year diminishing value so Y3 figures are like this 1000 New 80 % of Y2 New =800 80 % of Y1 remaining (800)=640 Can I get this one through a formula thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to get number + 80 % from Previous Balance
Hi. One way...
Assuming data in A1:B4: 0 $1,000 1 $800 2 $640 3 $512 Equation in B1 is the following, and drag down... =FV(-0.2,A1,,-1000,0) Total for years 0-3: =1000*(0.8^(3+1)-1)/(0.8-1) = 2,952 = = = HTH :) Dana DeLouis Abdul wrote: I have thefollowing Y1 Y2 Y3 Y4 1000 1000 1000 1000 800 800 800 640 640 512 Total 1000 1800 2440 2952 the logic like this Every year 1000 New Plus 80 % from previous year diminishing value so Y3 figures are like this 1000 New 80 % of Y2 New =800 80 % of Y1 remaining (800)=640 Can I get this one through a formula thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running balance from previous sheet | Excel Worksheet Functions | |||
previous number not changing | Excel Worksheet Functions | |||
sum previous x number of cells | Excel Discussion (Misc queries) | |||
Sum of previous offset number of cells not to exceed certain value | Excel Worksheet Functions | |||
rent received/balance owed/running balance spreadsheet | Excel Discussion (Misc queries) |