Loan Amortization Model broke

Hi all,

I have a pretty large portfolio loan amortization model I built. It's 45MB right now. I have a few issues I have been running into.
​
1- I utilized the error checking and i do not have any circular references but the model won't refresh unless I enable iterative calculations.

Any thoughts on this?

2- The model is huge , when I do run the iterations I have to wait till 200-300 iterations. Sometimes longer. Is there a way to clean it up and make it faster?

3- This model has too many stressed scenarios in my losses assumptions and I think I am using lots of memory. Whenever I make changes to logic and rerun the file breaks.

To give you an example it broke on my when i changes a formula, but i did not input a wrong formula, I did not mess up a refernce or anything.
After I pasted values for some tables i had that were TRANSPOSE formulas ,the file worked and ran the iterations.

Now i changed the same formula and just added 2 cells and it broke and won't run.

I ran out of ideas and don't know what to look for. Any ideas?

Originally Posted by hassan.farran

i do not have any circular references but the model won't refresh unless I enable iterative calculations. [....] I have to wait till 200-300 iterations.

That's a non sequitur.

Enabling iterative calculations is a benefit only when we have calculations that iterate over circular references. That's tautological on purpose.

So, why are you enabling iterative calculations?

Are you using Excel IRR, RATE or XIRR, and they return #NUM, and you are following MSFT's suggestion (click here) and enabling iterative calculations to remedy that?

AFAIK, that is a misdirection. If you have an example where enabling iterative calculations does indeed remedy a #NUM error, I really want to see it. If you are uncomfortable with attaching the Excel file here, let's make private arrangements. LMK.

Or are you using Goal Seek, and you find that changing max iterations and/or max change improves the result?

That is true. But we do not need to enable iterative calculations per se for the max options to work with Goal Seek.

Bottom line: Do not enable iterative calculations unless you (or a third-part app) wrote circular formulas on purpose.

And even then, we should avoid the need for circular references. Generally, they are inherently unreliable.

-----

Originally Posted by hassan.farran

Is there a way to clean it up and make it faster?

Yes.

But no one can offer specifics without at least a modicum of description of the design and the formulas.

And almost always, we need to see an actual Excel file itself.

To that end, you should follow the bright-yellow instructions at the top of the webpage to attach an example Excel that demonstrates the problem. Obviously something smaller than 45MB.

-----

Originally Posted by hassan.farran

don't know what to look for. Any ideas?

Neither can we. You have not described what you are doing.

AFAIK, there is nothing different between a "portfolio loan" amortization and any other loan amortization.

Are your referring to a portfolio of loans, like the Lending Club?

Even so, there is nothing about the amortization of a portfolio of loans that requires a "model", much less iterative calculations.

Do you really mean that you want to solve some problem that involves the use of a loan amortization at its core?

If so, what is that problem?

(It sounds like a simulation, perhaps. But a simulation of what is anyone's guess.)

Originally Posted by hassan.farran

thank you for expressing interest in this problem.

No pun intended?

-----

Originally Posted by hassan.farran

Here is the structure of my model [....blah, blah, blah....] Let me know if you want a small file upload

Absolutely! That is what I asked for previously.

-----

Originally Posted by hassan.farran

Only way for the amortization schedules to update is to let the iterations run.
I disabled iterations and went tab by tab to see if excel would flag a circular reference and it didn't.

So, what is driving the iterations, if not circular references?

Do you have any VBA code?

Do you have any third-party add-ins?

-----

Originally Posted by hassan.farran

Each amortization tab goes 4.5 years vertical (1037 cells) Jan 2022 to July 2027 , and horizontally I am amortizing till July 2027 , which is 67 months ahead.
[....]
After a specific time I let the model [...] to trigger an Asset Securitization.
[....]
So the model goes in circles, a securitization and amount will affect all formulas and all formulas need to run again.

Not to be nitpicking, but sometimes language semantics are important.

There is a big difference between "goes in circles [...] need to run again", which does indeed sound like circular references, and "goes in cycles" (or phases), which could be implemented with IF functions, for example, that affect the interpretation and behavior of cell formulas.

Again, this all might become clear (or clearer) if you provide a small working Excel file.

The small example Excel must demonstrate the dependency on Iterative Calculations.

That is, it "goes in circles [...] and run again" as intended only if Iterative Calculations is enabled.

And since you mention "each amortization tab", it sounds like the small example Excel file must have at least 2, preferably 3, "amortization tabs".

My guess is: that is the source of the circular references. IIRC, Excel is not always good any reporting "off-sheet" circular references. Sometimes yes; but sometimes not, I think.

-----

Originally Posted by hassan.farran

It's making me believe its just too much memory. When it breaks I will F9 a VALUE!

No, that is a classic symptom of circular references.

When Excel decides to stop the (endless?) "circle" of recalculations, random irrelevant cells can be left in inconsistent states.

I've seen inexplicable #NAME errors as well as #VALUE errors. And users have reported that Excel reports a circular reference in the simple formula =TODAY().

-----

Quite honestly, I don't know how much help I can be, because it sounds like a complicated design, and my time is limited.

I am primarily interested in understanding the dependency on Iterative Calculations.