Trying to work out the multiplier formula in Excel
Discussion
Not sure if this should be in here, but one for the math/excel boffs.
I am trying to work out how to multiply an amount with a percentage over a period of time.
So for example (and you can probably guess what i'm trying to do)
Cell 1A - Date
Cell 2A - 26/06/2007
Cell 1B - Amount
Cell 2B - £28.00
Cell 1C - Amount inc Interest at 8% on the 15/07/2009
Cell 2C - £32.57
Cell 1D - Amount inc Interest at 8% on the 22/10/2010
Cell 2D - £35.43
To give another example
Cell 1A - Date
Cell 3A - 14/12/2007
Cell 1B - Amount
Cell 3B - £38.00
Cell 1C - Amount inc Interest at 8% on the 15/07/2009
Cell 3C - £42.80
Cell 1D - Amount inc Interest at 8% on the 22/10/2010
Cell 3D - £46.68
So can anyone tell me how i am supposed to work out the formula so that i can produce an amount in column D?
Thanks in advance
DJ
I am trying to work out how to multiply an amount with a percentage over a period of time.
So for example (and you can probably guess what i'm trying to do)
Cell 1A - Date
Cell 2A - 26/06/2007
Cell 1B - Amount
Cell 2B - £28.00
Cell 1C - Amount inc Interest at 8% on the 15/07/2009
Cell 2C - £32.57
Cell 1D - Amount inc Interest at 8% on the 22/10/2010
Cell 2D - £35.43
To give another example
Cell 1A - Date
Cell 3A - 14/12/2007
Cell 1B - Amount
Cell 3B - £38.00
Cell 1C - Amount inc Interest at 8% on the 15/07/2009
Cell 3C - £42.80
Cell 1D - Amount inc Interest at 8% on the 22/10/2010
Cell 3D - £46.68
So can anyone tell me how i am supposed to work out the formula so that i can produce an amount in column D?
Thanks in advance
DJ
Enter the cell C1 date (15/7/2009) in cell F1
Enter the cell D1 date (22/10/2010) in cell F2
In cell E1 type: =NOMINAL(0.08,365)/365
This returns the daily interest rate at 8% (=0.000211, confirmed by typing =100*(1+E1)^365 which returns 108 (i.e. £100 plus 8% over 365 days))
In C2 replace the value £32.57 with the formula: =B2*(1+E1)^(F1-A2)
This returns the value of the amount at 15/07/07 as £32.79
In D2 replace the value £35.43 with the formula: =B2*(1+E1)^(F2-A2)
This returns the value of the amount at 22/10/10 as £36.17
The formula works by taking the initial amount (b2) and multiplying it by 100% plus the daily rate (1+E1) and raising it to the power of the number of days (^(F1-A2)). This effectively applies the interest rate for the required number of days.
Values are slightly different to yours; would be interested to know where you got them from?
Mike…
Edit: Did you get it by calculating the number of days difference, work it out as a fraction of a year, then multiply the initial amount by this?
If so, and you want to work it out this way, enter =B2*(1+(F1-A2)/365*0.08)
Enter the cell D1 date (22/10/2010) in cell F2
In cell E1 type: =NOMINAL(0.08,365)/365
This returns the daily interest rate at 8% (=0.000211, confirmed by typing =100*(1+E1)^365 which returns 108 (i.e. £100 plus 8% over 365 days))
In C2 replace the value £32.57 with the formula: =B2*(1+E1)^(F1-A2)
This returns the value of the amount at 15/07/07 as £32.79
In D2 replace the value £35.43 with the formula: =B2*(1+E1)^(F2-A2)
This returns the value of the amount at 22/10/10 as £36.17
The formula works by taking the initial amount (b2) and multiplying it by 100% plus the daily rate (1+E1) and raising it to the power of the number of days (^(F1-A2)). This effectively applies the interest rate for the required number of days.
Values are slightly different to yours; would be interested to know where you got them from?
Mike…
Edit: Did you get it by calculating the number of days difference, work it out as a fraction of a year, then multiply the initial amount by this?
If so, and you want to work it out this way, enter =B2*(1+(F1-A2)/365*0.08)
Edited by mike_knott on Thursday 21st October 22:06
mike_knott said:
Or you can enter:
=FV(NOMINAL(0.08,365)/365,F1-A2,0,28)
To do it by formula, but it essentially does the same as my first post.
Mike...
thanks mike - I've yet to try this, only just got home now to see this formula (PH is blocked in the office), will let you know the outcome in a bit. =FV(NOMINAL(0.08,365)/365,F1-A2,0,28)
To do it by formula, but it essentially does the same as my first post.
Mike...
mike_knott said:
_Deano said:
thanks mike - I've yet to try this, only just got home now to see this formula (PH is blocked in the office), will let you know the outcome in a bit.


Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff



e(SWIGGYWOMANBITS(yes, gimme, gimme=1, whoop,1,0))