Growing $100,000 with returns

[Kyle02]

Hey guys, first timer here. hope you can help me out.

I am trying to make a chart that grows 100,000 from a list of returns.

here is what i tried, but it doesnt work:

Growth: Format(100000+(DSum("[tblReturns].[Return]","tblReturns","[tblReturns].[ProductId]=" & [tblReturns].[ProductId] & " And [tblReturns].[MonthlyDate] <=#" & [tblReturns].[MonthlyDate] & "#")*100000),"00.00")

Now, In excel i have this set up by i am trying to get it into a DB. the numbers dont match...

In excel I do:
Product..Date....Return
A.............B.......C.......
In D column....
100,000*(1+C1)
and then
D1*(1+C2)
D2*(1+C3)
And so on...

Now with the Dsum formula i get these as my first 3 Values:


99517.27
101421.36
98855.42
and in excel
99517.27
101412.17
98810.99


Now i know the values in excel are right.. so how can i make it right in access?

[June7]

Not sure you will get them to agree. I ran into this issue when trying matrix multiplication with VBA in Access. Even when I used the Excel matrix functions in Access VBA the results were different from using the functions in Excel.

Want to provide data for analysis and testing? Follow instructions at bottom of my post.

[Kyle02]

Attached is a sample with data to look at

[June7]

The DSum essentially generates a running sum of Return so I replicated that in Excel. The following calc in Excel is the equivalent of the Access calc:

In column E:
=C2+E1
=C3+E2
...

In column F:
=100000+E2*100000
=100000+E3*100000
...

The results agree with the query.

[mike02]

When i look at it, it doesnt match the Data in excel. I think he needs the excel data. sry i cannot be of more help, I dont know how to get it.

Cheers,

[Kyle02]

Hey,

Thanks for the reply June7,

mike02 is right, the data that matches is what i have in the DB, therefore the Dsum formula is working correctley. This means i need to use something other then the Dsum formula, Any ideas? thanks,

[Kyle02]

Im think that if i set up a field in the query. IE>
Setup
1+Return

then i am part way there. all i need to to is run a sub query... to get the previous data..

(SELECT (T2.Return*Setup)      
FROM tblReturns AS T2     
WHERE T2.ProductID=tblReturns.[ProductID]  
AND T2.MonthlyDate < [tblReturns].MonthlyDate)

The issue here is though that the subquery wont return more than one value... So how can i manipulate that to run? or am will this not even work.
I looked into using left joins with a prior date, and using that to set up previous position, and current position. but that takes a long time to run, and. that data i get previous position from is not yet complete. that will be an issue with a subquery as well. does anyone have a work around idea?

[June7]

As shown in my previous post, I can get Excel to replicate the Access calc. I just don't understand the original Excel formula, why it is structured that way.

[Kyle02]

its structure like that to show the growth of the product, from this, you can calc, drawdown, Rate of return, etc.

I will be using it to create charts for my reports. It will show the growth of 100,000 if invest in the product since day 1. I have everything working in many excel spread sheets, but i am creating one big DB instead of many spreadsheets.

[June7]

Sorry, not strong with financial calcs. I don't understand why each Return % multiplies with the previous calc instead of the 100000 principle.

If calculation for ReturnOnInvestment % =Earnings/Investment*100

and

if Return is the ReturnOnInvestment % as of the date given then isn't the calculation of earnings =Return*Investment then add that to Investment for current principle as of that date?

So starting with $100000 on 1/31/05 you end up with $102476.94 on 7/31/13?

If I am way off base, this might be one of those cases where Access is not the best tool for the job.

[Kyle02]

because it grows on the previous amount. its a growing amount. on each month end, multiply 1*return by the previous amount, not 100,000. it is compounding.

[June7]

Compounding with a variable rate? Ugh!

Here's what I could accomplish.

1. created general module and code:

Option Compare Database
Option Explicit
Public CumGrowth As Double

Public Function CalcGrowth(dblRet As Double) As Double
CumGrowth = (dblRet + 1) * IIf(CumGrowth = 0, 100000, CumGrowth)
CalcGrowth = CumGrowth
End Function

2. created a report with tblReturns as RecordSource, added a textbox in detail section with expression in ControlSource
=CalcGrowth([Return])

3. opened report in print preview

4. for repeat runs of report, first reset CumGrowth back to 0 in the VBA immediate window
CumGrowth = 0
or build a form with code behind a button to reset the variable and open report

[Kyle02]

so i am wanting it to have parameters. I want a parameter to be ProductID, and have it reset on next productID, When i use this in a query, it Refreshes constantly and the values will not hold to be true.

Ie.

Where ProductID = ProductID
AND MonthlyDate<MonthlyDate

how does that work in Code?

I need it to tell access to take CumGrowth previous, Which it does, but once the product change it has to start at 0 again,

[June7]

That's why I instructed to build a report and call the function from a textbox. Display report in print preview, not report view.

Can have the report RecordSource be a filtered query using the parameters you want.

If you want all products to be on the report, the function will be more complicated. It will need to reset the CumGrowth. Something like:

Option Compare Database
Option Explicit
Public CumGrowth As Double
Public Product As Integer

Public Function CalcGrowth(dblRet As Double, intProductID As Integer) As Double
If Product <> intProductID Then
    Product = intProductID
    CumGrowth = 0
End If
CumGrowth = (dblRet + 1) * IIf(CumGrowth = 0, 100000, CumGrowth)
CalcGrowth = CumGrowth
End Function

Then call the function:
=GetGrowth([Return], [ProductID])

[Kyle02]

I figured thats why you made it in a report, but i need it on a query to run an analysis on it. how can i get it to stop refreshin? I assume it is just taking the last cumgrowth stored in memory and continuing the funtion. how could i put a For statement arounf cumGrowth, so i know it is using the last growth? and not necesarilly a random one?

Thanks, for all the help.