• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Financial Calculation

D

DonMinter

Member
I know how to do the various financial functions (pmt, fv, cumipmt). I am trying to figure out a FORMULA for determining the following:


Let us say I have an income that changes by an amount that can be determined by a formula. I want to put this income into an interest bearing account and calculate its future value after some period of time. For example, let's say that my formula for income is "increases 1% per period." For any given period, I can calculate from present value what the value of this will be during any period, however, I want a cumulative calculation over many periods.


There are formulas/functions to do the above for constant "payments," but I have found nothing that allows me to do this for a variable "payment."


Can someone lend some light to help me figure this out? As always, I'm asking for guidance, not answers. I have figured out a way to do this with arrays, but I want to be able to change variables such as length of time, and the array length would have to change to do this, so I'd then need to put this into a table or two. That's not good programming practice. I think I may have to actually do this with VBA, but I try to avoid VBA when a formula will do, as I'm still working on my VBA skills.


Thanks,

Don
 
Hi, Donminter!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Financial%20Calculation%20%28for%20DonMinter%20at%20chandoo.org%29.xlsx


Might it be the starting point? I'm still playing around with the formulas used in the Excel FV function, but nothing arises. However the developement for 35 months matches exactly with the 3rd. example for function FV in the built in Excel help.


Regards!
 
Thanks for the quick response, SirJB!


That's along the lines of what I had working. Your example is basically a table/array where you can do the calculations. I was hoping there was a formula; however, I'm going to go back and analyze the values. I'm nearly certain this can be done within the confines of math and formulas. I just need to figure out the relationship between a constant increase/decrease (acceleration? hmm) and a present value.


I think I just realized what to do. There's nothing like combining a little physics, mathematics, and finances to come up with an answer. If I look at the increase or decrease as a constant acceleration, I should be able to calculate the "velocity" at any given point in time. Combine that with the financial FV functions, and I might be able to do this after all.


Back to the keyboard! Thanks for pointing me in this new direction.


Don
 
Hi, DonMinter!


I already knew that I haven't invented the wheel or the powder gun, but I tried to develop each period value so as to imagine how to build a general formula.

Velocity1=Velocity0+Acceleration*Time

Wasn't it? In my thesis I assumed linear acceleration, but I don't know how your income growths. When back from the keyboard, just tell us if and how you did succeed.


Regards!
 
Hi, DonMinter!

Check this:

http://www.experts-exchange.com/Other/Math_Science/A_1948-A-Guide-to-the-PMT-FV-IPMT-and-PPMT-Functions.html

http://en.wikipedia.org/wiki/Annuity_(finance_theory)

Regards!
 
I have "answered" this post several times and erased my answer each time. I will not be posting my working spreadsheet. When I've completed the various sheets, I'll make them available to you, at least, SirJB.


In layman's terms, this equation became: calculate the present value as my deposit amount increases at one rate, but the account increases at a different rate.


This was the equivalent of having a thrust and an acceleration. The acceleration determined my increasing amount, and the thrust was the interest paid. Like I said, I had to think of this financial problem in physics terms. It then made sense and I was then able to write the formulas accordingly.
 
You must log in or register to reply here.
Back
Top