need help making Formula for estimating future date profit due to current rates

[ryankelley318]

Untitled.png

I need help figuring out how to estimate future profits going off current rates
In picture above the first 16 days of January the Balance increase was 143.66%, now if I wanted to estimate how much of a balance I would have at the end of January, how would i do that?
I have tried dividing the bottom cell (Total) by top cell (Total), then dividing it up by the number of days. but that brings to me to a near 9% per day increase. but if that was the case, doing by calculator, would bring me up to $40k within these 16 days. Pretty much I am wanting to make 12 cells in my spreadsheet that will give a rough estimate, for each month, where the *Total* will be at the end of that month. I need help, been trying to do this for hours. I can do it by calculator, but this is stumping me

[ryankelley318]

Now keep in mind, the *Profit* values will increase based on the *Total* values. Higher *Total* values = higher *Profit* values

[MrShorty]

Ignore this if someone comes along who inherently knows the formulas that should be used for this. As a non-financial person, I do not know those formulas, but, if you describe the process/algorithm/equations you use to compute this on a calculator, I expect that any of us will be able to help you program this into Excel.

If, perchance, your calculation fits into a present value/future value calculation, there is the built in FV() function: https://support.office.com/en-us/art...8-4fe4bb1b71b3

[ryankelley318]

so say if i had 10k to start the year, 15 days later into a 30 day month im at 15k. dividing 15k by 10k gives you 1.5 which means 150%. So in 15 days i increase 1.5x. so doing 1.5x15k ill be at 22,500. so by the end of the month i should be somewhere around 22,500. but since from day 1-15 i had a 150% increase, but from day 1-30 i had a 225% increase. I just want to be able to figure out how to get this into a formula to show estimation for end of January, end of Febuary, end of March etc etc

[MrShorty]

That sounds like a standard, basic compound interest problem. From an algebra tutorial site that I frequently reference: http://www.purplemath.com/modules/expofcns4.htm If my quick look at the function (see the help for the PV() function), this function is the same as the FV() function with a pmt of 0, though I could easily be wrong about that.

As posed in your OP, the problem looks like a more complex NPV() or XNPV() problem (see appropriate help files). At this point, though, the problem seems more like a business math problem, trying to figure out exactly what type of future value/investment/annuity type calculation you are trying to do. Once that is better defined, then it will be easier to help you (at least for those of us who have little training in the business math. I would expect that someone with that training may readily recognize the kind of problem you are working with here and be able to recommend the correct formula).

[CAntosh]

It's hard to tell exactly what approach to compounding the interest you're taking. A sample workbook (Use Go Advanced --> Manage Attachments) that shows your expected (hand calculated) results would help immensely. Going just off of your picture and my best guess as to your approach, I came up with the following:

=FV((($C3-$C$3)/$C$3)/$A3,($E$1-$A3),0,-$C3)

This formula assumes your day count is in column A, 'Profit' in column B, 'Total' in Column C, and projected days (months are different lengths...) in E1. The formula goes in D3 and gets filled down. The attachment should make things much clearer. I'll admit that I'm not an expert with Excel's financial functions, but if I'm understanding both you and Excel correctly, this formula's results appear to fit the logic of the problem. Hopefully I'm at least close?

EDIT: I fixed a stray "$" reference. Should be good now...