 # forecasting interest income from investment

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 3 Replies

# Re: 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.)

# Re: forecasting interest income from investment

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: forecasting interest income from investment

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.

-----