You have an estimate of the market value of a property, the age of the seller, and a technical rate. You launch a life annuity calculation and obtain a bouquet and a pension. But what happens if the market value drops by a few thousand euros, or if the technical rate changes by half a point?
One scenario is not enough to make a decision. An Excel life annuity simulator allows you to set multiple hypotheses side by side and see what really changes in the result.
Gross pension and net pension: two columns that should never be confused in a life annuity simulator
Most online calculators display a monthly pension without specifying whether it is gross or net. The difference between the two depends on the age of the annuitant and the applicable tax deductions. A seller over 69 years old will not be taxed on the same fraction of the pension as a younger seller.
In an Excel spreadsheet, the best practice is to systematically separate the gross pension and the net pension after deductions into distinct columns. Each price scenario then generates two lines of reading: what the buyer pays, and what the seller actually receives.
When you test three different market values with the proposed Excel life annuity simulator, you can compare not only the bouquets and pensions displayed but also the tax gap between each hypothesis. This is an angle that free online simulators almost always ignore.
Structure of an Excel file to compare life annuity price scenarios
Before multiplying the tabs, ask yourself a simple question: what variables do you really want to vary? In a life annuity calculation, three parameters change the game more than all the others.
- The market value of the property, which sets the basis for the entire calculation. Testing two or three estimates allows you to measure how sensitive the bouquet and the pension are to this figure.
- The technical rate, often set by default in online tools. By varying it in Excel (for example, by half a point), you can immediately see its effect on the monthly pension.
- The statistical duration of payment, related to the life expectancy of the seller. Using a single mortality table does not reflect the uncertainty. A good simulator allows you to test several durations in parallel.
The most readable structure is to place these three variables in rows, and each scenario in columns. You then obtain a comparison table that fits on a single screen, without having to navigate between multiple tabs.
Formulas to link together
The bouquet directly results from the market value minus the capital constituting the pension and the discount for the right of use and habitation (DUH). The pension, in turn, depends on the remaining capital, the technical rate, and the statistical duration. Each result cell must point to the hypothesis cells with absolute references. Changing a single hypothesis then updates the entire column of the scenario.
If you change the market value in cell B2 and all your formulas point to this cell, you can simply duplicate the column and modify B2 to create a new scenario in a few seconds.
DUH and discount: the parameter that free simulators freeze
The right of use and habitation is the most opaque variable in online simulators. It is often calculated automatically, without the user being able to modify it. In an occupied life annuity, the DUH discount can represent a significant part of the market value, and its estimation varies according to the methods used.
With a spreadsheet, you can test several DUH discount rates on the same property and observe the direct effect on the bouquet. A difference of a few points in discount can change the bouquet by several thousand euros, which alters the financial reading of the project.
Occupied life annuity or free life annuity: two models in the same file
The free life annuity does not involve a DUH since the seller vacates the property. The pension is therefore higher, and the bouquet lower at the same market value. Instead of creating two separate files, one tab per type of life annuity in the same workbook allows for a quick comparison of the two options.
You keep the same hypotheses for market value and technical rate, and only the DUH parameter changes. The comparison becomes clear.
Reading the results: what to look at first in a scenario table
A table with six columns and fifteen rows can quickly drown out the information. Two reflexes help maintain a clear reading.
The first: identify the scenario where the net pension after tax is the most stable despite variations in market value. A robust scenario is one that remains consistent even if the property estimate varies by a few percent.
The second: compare the bouquet between occupied and free life annuity for the same market value. If the bouquet difference does not compensate for the pension difference over the statistical duration, one of the two formats is clearly more advantageous.
- Check that the technical rate used corresponds to market practices and not to a default rate from the spreadsheet.
- Ensure that the mortality table used is consistent with the age and sex of the annuitant.
- Review the DUH formulas: a cell reference error can skew the entire scenario without triggering a visible error message.
An Excel life annuity simulator does not need to be sophisticated to be useful. Its strength lies in making visible what online tools hide: the hypotheses, the gaps between scenarios, and the sensitivity of the result to each parameter. Three well-constructed columns are worth more than a form that returns a single number without explanation.