Forum Discussion
forecasting interest income from investment
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.)
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?
- Re: ``wish to calculate the remaining 5 months what I can expect to earn in interest given the steady trend``
Given the dearth of details, we cannot forecast realistically. For all we know, the pattern is cyclical. Or not at all!
-----
Re: ``How can I [...] use the information to forecast the next fiscal year 2022?``
We really cannot. At the very least, you must specify how fiscal year 2022 might differ from fiscal year 2021 with respect to the investment income.
-----
Your questions are really too broad for anyone to offer a useful answer.
Forecasting is as much art as it is science. No single approach works for all situations. And blindly applying one approach or another based on unfounded assumptions leads to useless forecasts. GIGO!
The more specific you are, with concrete examples, the better.
The essence of forecasting is to discover patterns in the past. One way to do that is to start with an XY Scatter chart of the data.
That is what I did in the attachment to my previous response. That does not show a "steady trend".
But a forecast might also be influenced by assumptions about the future.