Trying to work out the multiplier formula in Excel
Trying to work out the multiplier formula in Excel
Author
Discussion

_Deano

Original Poster:

7,414 posts

284 months

Thursday 21st October 2010
quotequote all
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

mikeh501

800 posts

212 months

Thursday 21st October 2010
quotequote all
Isn't there some kind of compound interest function in excel?

_Deano

Original Poster:

7,414 posts

284 months

Thursday 21st October 2010
quotequote all
mikeh501 said:
Isn't there some kind of compound interest function in excel?
no idea, but i will look up compound interest now.

RichTbiscuit

3,266 posts

202 months

Thursday 21st October 2010
quotequote all
Edit: misinterpreted the post

I'm assuming it's not as simple as just taking the previous amount and multiplying by 1.08?

Edited by RichTbiscuit on Thursday 21st October 21:53

mike_knott

344 posts

255 months

Thursday 21st October 2010
quotequote all
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)


Edited by mike_knott on Thursday 21st October 22:06

mike_knott

344 posts

255 months

Friday 22nd October 2010
quotequote all
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...

_Deano

Original Poster:

7,414 posts

284 months

Friday 22nd October 2010
quotequote all
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.

mike_knott

344 posts

255 months

Saturday 23rd October 2010
quotequote all
_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.
wavey

_Deano

Original Poster:

7,414 posts

284 months

Sunday 24th October 2010
quotequote all
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.
wavey
Sorry Mike, i was on call and was called into work yesterday - just about to hit the sack, so i will give this a go tomorrow morning. Thanks again though. smile

zollburgers

1,285 posts

214 months

Sunday 24th October 2010
quotequote all
Try:

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

_Deano

Original Poster:

7,414 posts

284 months

Tuesday 26th October 2010
quotequote all
Hi Mike wavey
That worked a treat, the original "with interest" amounts were created from the moneysavingexpert website, but i didn't fancy having to enter every one of the dates and amounts that are already in a spread sheet.
Thanks again for the formula

DJ

mike_knott

344 posts

255 months

Tuesday 26th October 2010
quotequote all
smile

Dogwatch

6,385 posts

253 months

Tuesday 26th October 2010
quotequote all
zollburgers said:
Try:

=(GIRLwe(SWIGGYWOMANBITS(yes, gimme, gimme=1, whoop,1,0))
Fail! - brackets don't pair off wink

zollburgers

1,285 posts

214 months

Tuesday 26th October 2010
quotequote all
Dogwatch said:
zollburgers said:
Try:

=(GIRLwe(SWIGGYWOMANBITS(yes, gimme, gimme=1, whoop,1,0))smellybutyay)
Fail! - brackets don't pair off wink
Fixed?

-Pete-

2,914 posts

207 months

Tuesday 26th October 2010
quotequote all
F1?

donz29

386 posts

236 months

Tuesday 26th October 2010
quotequote all
That's some serious late payers you've got there! wink