offset function in excel

Recently I realized that for portfolio management related excel modelling the most powerful function that excel has is "offset". It is as if Microsoft included it in excel to create dynamic portfolios. It is usually used by the people as an ordinary function. However it can be used to create dynamic arrays- something we use quite a lot in portfolio modelling- and now one can develop a completely dynamic portfolio model without using VBA.

There is a cost however. It slows down the machine speed considerably particularly if one has also used a Table in the model to create the efficient frontier.

The secret in using the Offset function is: keep both the row-ref and column-ref zero, and use the height and breadth using some dynamic reference. You will see real magic in your portfolio models.