Feb 23 2021 11:31 AM
Hello,
I'm trying to use the formula to forecast the remaining months of interest income, Feb 2021 to 6/30/21. Any help you can give would be helpful
Feb 23 2021 03:14 PM - edited Feb 23 2021 03:33 PM
Olga, first, you should explain why the formula is not working for you. I presume you get a #N/A error. Well, that is what __I__ get.
The reason is: in the Name Manager, we see that Amntrange is ='Journal Summary Export'!$B$5:$B$11, whereas Effdaterange is ='Journal Summary Export'!$A$5:$A$12. The FORECAST function requires that the sizes of the two parameters are the same. In this case $A$11 is correct.
I avoid the use of named ranges for the very reason that it is difficult to see such mistakes. So I would write: =FORECAST(A12,$B$5:$B$11,$A$5:$A$11).
However, GIGO!
The question is: is a linear trendline even correct for your data?
The attached chart demonstrates that the answer is "no". (I removed the named ranges.)
The interest repayment pattern seems to follow a sigmoid logistic function. My "sigmoid skills" are weak. It might take me some time to provide an adequate estimator function. (Someone else might do it sooner.)
Feb 24 2021 07:44 AM
Thank you Joe. This is my situation:
Each month we receive interest income from short-term investments at a given rate. I'm 7 months into the fiscal year and wish to calculate the remaining 5 months what I can expect to earn in interest given the steady trend. How can I calculate that using excel and moreover, use the information to forecast the next fiscal year 2022?
Feb 24 2021 03:45 PM