Excel OFFSET Function for Calculating Financial Returns Part 1

Learn how to use the Excel OFFSET Function for Calculating Financial Returns, automate your return calculations and save time!

Avoid manually update your financial return Excel Formulas with the OFFSET function. This function when used with a Named Ranged allows us to create a DYNAMIC RANGE that will ROLL over every time we input new data.

When you master this Excel function you can easily create automatic Factsheet for your portfolio of investments, you'll just have to insert new data and all the formulas will always give you the most up to date performance returns

Formulas:

12 MONTHS Return (Returns start in cell B2 and B1 is the header)

=OFFSET(Main!$B$1,COUNT(Main!$B:$B),0,-12,1)

36 MONTHS Return (Returns start in cell B2 and B1 is the header)

=OFFSET(Main!$B$1,COUNT(Main!$B:$B),0,-36,1)

#offsetfunction #offsetformula #excelforfinance

[myleslloyd]

I love how you used COUNT as the step size. Very cheeky! Work smarter not harder.