My Math Forum  

Go Back   My Math Forum > Science Forums > Economics

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


Reply
 
LinkBack Thread Tools Display Modes
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!
redliner74 is offline  
 
July 17th, 2013, 05:27 AM   #2
Global Moderator
 
CRGreathouse's Avatar
 
Joined: Nov 2006
From: UTC -5

Posts: 16,046
Thanks: 937

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.
CRGreathouse is offline  
July 17th, 2013, 06:03 AM   #3
Math Team
 
Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 12,112
Thanks: 799

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....
Denis is online now  
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!
redliner74 is offline  
Reply

  My Math Forum > Science Forums > Economics

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 04:54 AM
Compound Interest Q2 suood Elementary Math 3 July 31st, 2013 08:34 PM
Compound Interest Help Alexis87 Algebra 3 May 13th, 2013 12:40 PM
Simple interest/Compound interest Qwertyqwerty Elementary Math 2 September 20th, 2011 10:41 PM
Compound interest? JerryAtric Calculus 3 March 6th, 2009 07:23 PM





Copyright © 2018 My Math Forum. All rights reserved.