SENSITIVITY TABLE
Transcript
Welcome to this video. In the video, I am going to cover the sensitivity table in the ARGUS Excel Model.
Specialized Sensitivity Table
I can see the sensitivity table is massive. A lot of people would go to the Data tab, What-If Analysis, and Data Table to create their sensitivity table. However, this specialized sensitivity table is different than a regular data table in many ways. Let’s get into it.
Row Header and Column Header
The row header is the Exit Cap Rate, which I will input 8.5%. The column header is the Going-in Cap Rate. I will go back to the Input tab. Copy the going-in cap rate in the Other Assumptions Section. Going back to the Return Sensitivity Table tab. Paste it. I can see all the exit cap rates are the same so I will input 0.5% for the Exit Cap Rate Incremental. Now the Exit Cap Rate ranges from 7% to 10%. I will also input 0.5% for the going-in cap rate incremental.
How to Read the Table
Click the Update Table button. A progress bar pops up. Because of the massive calculation the computer is doing, it might take up to 15 minutes to update the sensitivity table. I am going to fast forward a little bit.
The update finishes and I can start reading the table. I am going to zoom out a little bit. I can see the table consists of 49 blocks. There are 7 blocks in the first row and each block corresponds to different Exit Cap Rate but they share the 6.77% Going-in Cap Rate. Likewise, there are 7 blocks in the first column and each block corresponds to different Going-in Cap Rate but they share the 7% Exit Cap Rate. Therefore, the first block stands for the returns with a 7% Exit Cap Rate and a 6.77% Going-in Cap Rate. The second block stands for the returns with a 7.5% Exit Cap Rate and a 6.77% Going-in Cap Rate. I can keep reading the blocks this way. Let’s zoom in a little bit. I can see each block is made up of 9 cells. The block row header is Deal Level, GP, and LP. The block column header is IRR, Multiple, and Net Profit. In the first block, the 14% is the deal level IRR and the 23% is the GP IRR.
To be more precise, the 14% is the deal level IRR with 7% Exit Cap Rate and 6.77% Going-in Cap Rate. The 23% is the GP IRR with 7% exit cap rate and 6.77% going-in cap rate. Let’s go to the middle block. With an 8.5% Exit Cap Rate and an 8.3% Going-in Cap Rate, The GP IRR is about 30% and GP Multiple is about 3.2. The LP IRR is about 17% and LP Multiple is about 1.9. The best-case scenario is the left-bottom block where the going-in cap rate is 9.77% and the exit cap rate is 7%. The worst-case scenario is the top-right block where the going-in cap rate is 6.77% and the exit cap rate is 10%.
This table provides comprehensive return analysis on different going-in cap rates and exit cap rates.
I have finished the sensitivity table section in the ARGUS Excel Model. Thanks for watching this video. I will see you in the next one.