Annualized Total Return - Help!

[Colin Middel]

Hey all - Relatively new to the REI market. Recently built a spreadsheet for myself to analyze properties and get a better feel for the market. I have a lot of similar characteristics from the online tool on BP. However, I have made some changes that fit my eye better to provide a more thorough cash flow & investment analysis.

One problem I am having trouble with, (this is literally the last piece i am looking to add before I get more complex with what the spreadsheet spits out: taxes, market fluctuations, etc...) is the "Annualized Total Return" analysis on the BP calculator. Part of why I am building my own spreadsheet is to get comfortable with the numbers, and to not just know what good returns are, but to truly understand the story behind the numbers. When I calculate out the annualized return on my spreadsheet, my first year always matches the first year on BP, however every single year after that is wrong. I understand it is the average over the entire investment.  Does this require a geometric average?  

This is my formula. Annualized Total Return = (Total Profit if Sold / Total Cash Needed to Purchase Property) / Year X

Year X = whatever year it is. 

To boil it all down, can someone provide me with an in depth definition and formula based on the BP calculator to correct my excel spreadsheet? My Total Profit if sold  and total cash needed numbers are exactly the same as the BP calculator.