
Economics Economics Forum  Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance 
 LinkBack  Thread Tools  Display Modes 
July 17th, 2013, 02:48 AM  #1 
Newbie Joined: Jul 2013 Posts: 2 Thanks: 0  Compound Interest and Target projections in Excel
Hi guys, I'm battling a bit with compound interest over different periods, and how to look at shorter terms gains, and gauge if those shorter term gains are in line with my longer term goals. Let's start with $10'000.00 My goal is between 10%20% per month increase on that. Just trying to keep the figures simple. I check what is happening with that money daily, let's say at 8pm. So every evening at 8pm, I check the latest figure on that $10k. Now to keep everything nice and homogenous, I work my months not as calender months, but as months, each with 30.4375 days. Every week has 7 days, of which 5 days are working days in which gains can be achieved. Gains are continually compounded, but generally the smallest increment I check at, is daily. So... Day 1: 10000 Day 2: 10030 Day 3: 10050 Day 4: 10060 Day 5: 10100 Day 6: 10100 Day 7: 10100 Day 8: 10095 Day 9: 10105 Day 10: 10130 Day 11: 10160 Day 12: 10180 Day 13: 10180 Day 14: 10180 As those days tick by, I want to know, given the data that I have at the time, let's say day 3, if that is within my 10%20% per month target. I basically need to calculate compound interest per month, but on daily and weekly times scales. I also need to know how much 10 and 20% compound interest works out to weekly compound interest? So when I enter day 10's figures for instance, I will know that I'm on/below/above my weekly target, and I'm on/below/above my monthly target. So week one could've been a bad week, where I missed my weekly target, but week 2 can be better, so I might still be on track for my monthly target. Just through trial and error I've calculated that 10% per month compound interest, works out to about PreviousDayValue*1.006377 for a daily gain figure. 20% per month is about PreviousDayValue*1.012228 for a daily gain figure. Those are the targets. When I punch in day3's figures, I want a formula that tells me what that gain translates to in terms of weekly compound, monthly compound, and maybe even yearly compound. I hope I managed to convey what I'm looking for And I'd be much appreciative of any help you guys can give! 
July 17th, 2013, 06:27 AM  #2 
Global Moderator Joined: Nov 2006 From: UTC 5 Posts: 16,046 Thanks: 938 Math Focus: Number theory, computational mathematics, combinatorics, FOM, symbolic logic, TCS, algorithms  Re: Compound Interest and Target projections in Excel
You have a return on one day and you'd like to convert it to a return per month. Each month has 30.4375 days, so if you make a return of x (say 0.01 = 1%) on a day that is equivalent to a monthly return of (1 + x)^30.4375. On the flip side, if you want to make 10% to 20% a month (I'll refrain from commenting on that goal) you need to make between 1.1^(1/30.4375)  1 and 1.2^(1/30.4375)  1 return per day on (geometric) average. To lots of decimal places, that is 0.31362483% to 0.60080067% per day.

July 17th, 2013, 07:03 AM  #3 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 13,796 Thanks: 970  Re: Compound Interest and Target projections in Excel
Hmmm....hard to tell what you're after...so, here's a bit of a guess : Using this "so far" result shown in your example: "Day 10: 10130"; I'll assume 30day months or 360 days. Per day so far: (10130  10000) / 10 = 13 I year at above: 360 * 13 = 4680 Value after 1 year: 10000 + 4680 = 14680 : clearly 46.8% annual Convert to, as example, monthly cpd. rate: (1 + m)^12 = 1 + 46.8/100 (1 + m)^12 = 1.468 1 + m = 1.468^(1/12) m = 1.468^(1/12)  1 m = ~.0325 or 3 1/4 % Other cpd. frequencies can be similarly obtained. And if you're doing this for the Board of Directors, you can easily add impressive stuff like : even if we're off 20% either way, our annual yield will be from 37.44% to 56.16% You're then quite sure to keep your job.... 
July 18th, 2013, 12:04 AM  #4 
Newbie Joined: Jul 2013 Posts: 2 Thanks: 0  Re: Compound Interest and Target projections in Excel
Thanks guys! Got it working nicely now! I have 4 columns now F, H, J, K F=Actual figures H=Projected Interest/Growth per month, based on the figures in F J=Target figures calculated at 8% per month, compounded daily K=Target figures calculated at 18% per month, compounded daily So the starting figure in F2,J2,K2 are all the same. 11215 Row 1 just has my headers. For J3 onwards, and K3 onwards, my formula now looks like this. =J2*(1+0.08/22)^(1) The J2 in the formula will change to J3, J4, J5 etc as the formula gets copied. It always uses the value directly above it. Some of the formula is redundant, but I keep it there to remind me how it works. The 0.08 is obviously the 8%. That changes to 0.18 for column K which is my 18% column, and the fist reference is the K2. The 22 is my 22 working days per month. It was just easier than 21.xyzabc decimal working days. The raising to the power (1) was my "how often is it compounded", which was actually (22/22). So that formula gave me my upper and lower target curves. I then wrote that formula on a piece of paper and transposed all the factors, to get the growth (0.08 and 0.1 as the answer. So I could then populate column H. So in H3 is: =22*((F3/$F$2)^(1/1)1) In H4 is: =22*((F4/$F$2)^(1/2)1) In H5 is: =22*((F5/$F$2)^(1/3)1) And that works beautifully! That formula gets copied all the way to 22 working days, and then restarts from ^(1/1) for the first day of the new month. The $F$2 will then refer to the last figure of the previous month. So when I punch in my first actual figure for the new month, I immediately know if that figure is between 8 and 18% or not. So I can start planning very early in the month. The work is of such a nature that it's continuous, and does not require startup times etc. So if I'm out on day 1, I know I'm out, and have to do something about it immediately. I also know that the day I am back in the 818%, I don't have to over compensate to actually drag the figure up. That's taken care of in the formula. Once I'm in my green zone, I really am there, no further adjustments required. Unless I ended a month outside the green zone, and use that figure to start the new month. Thanks for all the help guys! 

Tags 
compound, excel, interest, projections, target 
Search tags for this page 
Click on a term to search for related topics.

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Compound Interest Q3  suood  Elementary Math  3  October 26th, 2013 05:54 AM 
Compound Interest Q2  suood  Elementary Math  3  July 31st, 2013 09:34 PM 
Compound Interest Help  Alexis87  Algebra  3  May 13th, 2013 01:40 PM 
Simple interest/Compound interest  Qwertyqwerty  Elementary Math  2  September 20th, 2011 11:41 PM 
Compound interest?  JerryAtric  Calculus  3  March 6th, 2009 08:23 PM 