Should be a simple question for those that understand

threeg5

New Member
Joined
Mar 9, 2011
Messages
4
Hello and first thank you for reading

I have a formula
=StartingBalance * (1 + Rate)^NumberOfDays

this is to create a daily compounded interested rate

now if this formula is correct (that is what I am checking)

Principal = 400
interest rate = 3%
number of days = 1 year

then
=400*(1+.03)^365 ( is this correct for trying to get what I am looking for which is $400 at 3% interested compounded daily over a year)

This would equal 19,393,089.81

I find something wrong with this as it is a high number that I was not prepared for.

also is there a variable that can be inserted in this equation for a variance on the interest rate at varying times. as some days may be 0% and some may be -100%(the interest would be lost not the principal)

thanks
3g5
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
hi and welcome to the board

your problem is that you have used the yearly rate, but calculated daily.

rate should be .03/365 for daily.

ie
Code:
=400*(1+.03/365)^365

your code did 365 years, thats why its so high.
 
Upvote 0
Wow! Who exactly are you borrowing money from that charges 3% per day interest?! :eek: I'm not sure what loan sharks charge, but surely their rates would be better than what you're getting.

If that 3% is meant to be an annual rate then you need

= p × (1 + i / 365)<SUP>365</SUP>
 
Upvote 0
Never Mind....too slow again !!!

Hi P:
 
Upvote 0
honestly it is not that I am borrowing anything for a 3% interest.

I am looking at increasing my principal by 3% daily

so if I start with an investment in me at 400
and I am going at 3% a day then how much will be made after one year if it is compounded
so day 1 will end with 412
day 2 ends with 424.36
day 3 is 437.09
day 4 is 450.21
etc....

there is a calculation for that and I am not incllined enough to figure it out right now. be it brain fry looking to hard trying to hard or just be unthoughtful
:ROFLMAO:

Thanks again
3g5
 
Upvote 0
Wow! Who exactly are you borrowing money from that charges 3% per day interest?! :eek: I'm not sure what loan sharks charge, but surely their rates would be better than what you're getting.

If that 3% is meant to be an annual rate then you need

= p × (1 + i / 365)<SUP>365</SUP>


This just gives me the return for 1 day (well what I would like) which is 12.18

I guess maybe I would need to do this enitre formula ^365?
Brian broke can't fix smells like smoke tastes like chocolate
 
Upvote 0
simple answer guys is that I have the answer at 85081.51(well roughly that is minus 5 days for vacation(so it is 360 days) i would like to know the formula for this that can be inserted into an excel/openoffice spreadsheet so that I can tweak the "interest" number for a variance.:eek:

I know this is not a math forum but it is an excel formula forum
why does this have to be so hard:mad::confused:


and before some asks i could just do this again the same wya got the above answer but, I did it manually and it would be alot easier if I could just change the dangum "interest rate" to adjust for days that are more or less then the desired return:biggrin:

3g5
 
Upvote 0
...I am looking at increasing my principal by 3% daily

so if I start with an investment in me at 400 and I am going at 3% a day then how much will be made after one year if it is compounded
so day 1 will end with 412
day 2 ends with 424.36
day 3 is 437.09
day 4 is 450.21
etc....

your original formula is right and so is the end number

Yes - that is the "magic" of compounded interest. If the number seems high, think of it this way - if you had invested $400 in the year 1646 and averaged a 3% annual return for over three and a half centuries, would you be surprised to have $19 million?

Also in this context you can use the "rule of 72" to see if the number makes sense. The rule of 72 says that on compounding things like interested paid or inflation, you can estimate how long it takes for the principal (or price index) to double by dividing 72 by the interest rate. So with this rule of thumb we can estimate that to double...

@ 1% ≈ 72 periods (reality = 70<SUP>th</SUP> period)
@ 2% ≈ 36 periods (reality = 29<SUP>th</SUP> period)
@ 3% ≈ 24 periods (reality = 24<SUP>th</SUP> period)
@ 4% ≈ 18 periods (reality = 18<SUP>th</SUP> period)
@ 5% ≈ 14 periods (reality = 15<SUP>th</SUP> period)
@ 6% ≈ 12 periods (reality = 12<SUP>th</SUP> period)
...

So @ 3% we should see your amount double about every 24 periods. 365 / 24 = 15.2 doublings. Knock that down to 15 and 2<SUP>15</SUP> is 32,768 - so just ballparking it with nothing more than the "rule of 72" we'd guesstimate our answer should lie just a little north of $13 million and it does.

Or -- since this is Excel and we want to see it grow day-by-day, we can simply do this:

A1: =DATE(2011,1,ROW())
B1: =principal*(1+interest)^ROW()

Format A1 as dd-mm-yy and autofill down 365 rows.

Or if we want to see how it grows month-by-month we can do this:

D1: =DATE(2011,ROW()+1,1)-1
E1: =principal*(1+interest)^(D1-"31-Dec-2010")

and autofill down 12 rows.

All of this, assuming of course, that you have named ranges which correspond to principal and interest.

<pre>01-Jan-11 412.00 31-Jan-11 1,000.03
02-Jan-11 424.36 28-Feb-11 2,288.00
03-Jan-11 437.09 31-Mar-11 5,720.19
04-Jan-11 450.20 30-Apr-11 13,884.39
05-Jan-11 463.71 31-May-11 34,712.10
06-Jan-11 477.62 30-Jun-11 84,255.38
07-Jan-11 491.95 31-Jul-11 210,645.23
08-Jan-11 506.71 31-Aug-11 526,630.00
09-Jan-11 521.91 30-Sep-11 1,278,269.23
10-Jan-11 537.57 31-Oct-11 3,195,775.79
11-Jan-11 553.69 30-Nov-11 7,756,986.63
12-Jan-11 570.30 31-Dec-11 19,393,089.81

</pre>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top