My Math Forum Compound Interest and Target projections in Excel

 Economics Economics Forum - Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance

 July 17th, 2013, 01: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, 05: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, 06:03 AM #3 Math Team   Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 13,294 Thanks: 932 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 17th, 2013, 11:04 PM #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 start-up 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 8-18%, 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

### what is math figure 30.4375 for months

Click on a term to search for related topics.
 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post suood Elementary Math 3 October 26th, 2013 04:54 AM suood Elementary Math 3 July 31st, 2013 08:34 PM Alexis87 Algebra 3 May 13th, 2013 12:40 PM Qwertyqwerty Elementary Math 2 September 20th, 2011 10:41 PM JerryAtric Calculus 3 March 6th, 2009 07:23 PM

 Contact - Home - Forums - Cryptocurrency Forum - Top