Forum Discussion
Formula to reduce a number by 10% each day
I am looking for a formula that will reduce an amount by 10% each day. Example: Day 1 - $60,000*10%=$6,000. $60,000 - $6,000= $54,000. Day 2 - $54,000*10%= $5,400. $54,000 - $5,400= $48,600. I am working on a ACPI x64-based PC using Windows 10.
9 Replies
The recurrence formula defines a geometric series and the amount corresponding to any given day is
= initial*(1-Rate)^pUsing 365, the formula to generate a table, such as presented by HansVogelaar, would be
= LET( p, SEQUENCE(Days-1), initial*(1-Rate)^p)For more complicated calculations in which the rate varies there will, in future, be formulas such as
= LET( rateByDay, SEQUENCE(Days-1,1,Rate,0), SCAN(initial, rateByDay, LAMBDA(acc,r, acc*(1-r))) )but those functions are still in beta release.
=A2*(1-10%)
or
A2*0,9
Take a look on your calculation because
54000-5400 =48600
- Yes, I fixed my calculation. I need this calculated in one cell. Basically, it is a 10% reduction for every day late a vendor submits a report. Would I use an IF formula for that?
- You can have a differente approach.
You can stablish a starting date and make a calculation with the difference between actual date and the starting date.
Lets say
A2=starting date
B2= Amount
C2=B2*(1-(0,1*(TODAY()-A2)))
Let's say the initial amount is in A2.
In A3, enter the formula =90%*A2
Fill or copy down from A3.
Or if you prefer the values in a row, enter the formula =90%*A2 in B2, then fill or copy to the right from B2.
- How do I get this to calculate in one cell? Use an IF formula?
You'd need VBA code for that, but I wouldn't recommend it.
It's easier to create a list with dates and the corresponding amounts:
