VBA for Financial Models in Excel
VBA, a scripting language built into Excel, allows for automation of repetitive tasks, complex
calculations and data manipulations. With VBA, we can run Excel automations more efficiently
and produce calculations more efficiently, all while reducing human error and protecting your IP.
In-Depth Guide: Automating IRR and Target Pricing with VBA
In the Mastermind session, we used the case study below to demonstrate how VBA can be a
powerful tool in real estate financial analysis.
1. Setting Up The Spreadsheet
Initial inputs:
- Property Price: The initial investment amount (used: £1,000).
- Rent: The annual rental income (used: £100).
- Exit Value: The anticipated future value of the property (used: £1,200).
- Target Rate: The desired internal rate of return (IRR) (used: 15%).
Ensure that you have an IRR formula configured to calculate returns based on these values
(use the IRR syntax in Excel).
2. Recording a Macro: The Starting Point
Recording a macro is an excellent starting point for automating your processes. Here’s a step-
by-step guide:
Access the Developer Tab: Go to Developer > Record Macro in Excel. If you don’t see the
Developer tab, you might need to enable it through Excel Options.
Name Your Macro: Choose a descriptive name, such as “Calculate_IRR_Target_Price”.
Record Your Actions:
Copy the Property Price: Select the cell containing the property price and copy it.
Paste to a New Cell: Paste the value into a new cell that won’t interfere with other calculations.
Apply the IRR Formula: Use the IRR function in a different cell to calculate the return based on
the pasted values.
Run Goal Seek: Navigate to Data > What-If Analysis > Goal Seek, set it to adjust the property
price until the IRR matches your target rate.
Copy and Paste Results: Copy the result of the Goal Seek process and paste it back into the
original cell.
Clean Up: Remove any temporary data used during the process.
Stop Recording: Finalise your macro by stopping the recording process.
3. Refining Your VBA Code
After recording, you might need to adjust your VBA code for precision. Here’s a refined VBA
script:
Copy our code below:
Sub Calculate_IRR_Target_Price()
' Copy the property price from the original cell
Range("C4").Copy Destination:=Range("D4")
' Apply the IRR formula to the new location
Range("G4").Formula = "=IRR(C4:C14)"
' Use Goal Seek to adjust the property price to achieve the target IRR
Range("G4").GoalSeek Goal:=0.15, ChangingCell:=Range("C4")
' Copy the adjusted price and clean up temporary cells
Range("C4").Copy
Range("D4").PasteSpecial Paste:=xlPasteValues
' Restore the original property price
Range("C4").Value = Range("D4").Value
Range("D4").ClearContents
Range("A1").Select
End Sub
4. Adding User-Friendly Features
Enhance accessibility by adding a button to your worksheet:
1. Insert a Button: Navigate to Insert > Shape, choose a button shape, and place it on
your worksheet.
2. Assign the Macro: Right-click the button, select Assign Macro, and link it to
“Calculate_IRR_Target_Price”.
3. Label the Button: Clearly name it, such as “Run IRR Calculation”, to indicate its
function.
5. Testing and Validation
Thorough testing is essential. Change various inputs and verify that the macro performs
correctly across different scenarios. This step ensures reliability and accuracy in your financial
models.
Conclusion
VBA is more than just an advanced tool; it’s a game-changer for financial modelling. By
automating complex calculations and streamlining workflows, VBA allows you to focus on
strategic insights rather than manual data entry. The techniques shared in this mastermind
session offer a robust foundation for mastering VBA, enhancing your efficiency, and driving
better financial outcomes.
If you found this guide valuable or have any questions about VBA and financial modelling, feel
free to reach out. We’re here to support your journey towards financial excellence. Embrace the
power of VBA and take your financial modelling to new heights!
Share on
Posted on Sep 11, 2024 by
