The life annuity relies on a balance between the initial payment, the annuity, and the estimated duration of the contract. Changing a single parameter (the seller’s age, the discount rate, the amount of the initial payment) shifts the profitability from one scenario to another. An Excel spreadsheet allows these variables to be laid out side by side, but most files circulating online limit themselves to a single calculation, without real comparison between hypotheses.
The goal is not to produce a figure, but to visualize how several combinations of initial payment/annuity react when we vary the life expectancy or the seller’s taxation.
Net annuity after taxes: the parameter that most simulators ignore
Comparing two annuity scenarios in gross amounts skews the ranking. The taxable portion of the life annuity depends on the age of the annuitant when the annuity begins: 30% taxable from age 70, 40% between 60 and 69 years, 50% between 50 and 59 years, 70% before age 50 (Article 158, 6 of the CGI). In addition, social contributions of 17.2% are applied to this same portion.
In a spreadsheet, this translates into an additional column. First, the taxable portion is applied according to age, then the seller’s marginal tax rate, followed by the social contributions. The result, the net annuity received, often changes the order of preference between two scenarios that seemed close in gross terms.
Before multiplying the hypotheses, it is worth understanding how to use a structured Excel life annuity simulator to integrate this tax calculation from the outset, rather than tacking it on afterward to a file designed solely in gross terms.
Excel structure to compare multiple life annuity price scenarios
The scenario manager integrated into Excel (Data > What-If Analysis) allows for storing multiple sets of variables and switching from one to another. In life annuity, the input cells are at a minimum the market value of the property, the percentage of the initial payment, the occupancy discount rate (in occupied life annuity), the selected life expectancy, and the annual revaluation rate of the annuity.
Input cells and output cells
The output cells calculate the initial payment in euros, the gross monthly annuity, the net annuity after tax, and the total discounted cost for the buyer. The idea is to create three or four named scenarios (for example: short, median, long life expectancy) and compare the results in a summary report automatically generated by Excel.
- Scenario A: high initial payment, low annuity, median life expectancy. Suitable for a seller who wants to secure immediate capital.
- Scenario B: reduced initial payment, higher annuity, same life expectancy. Interesting for a seller whose tax bracket remains low.
- Scenario C: same parameters as B, but with an extended life expectancy of several years. Allows measuring the longevity risk for the buyer.
The Excel two-way data table complements this setup. In rows, the tested initial payment amounts are placed; in columns, the estimated life durations. Each cell in the table then displays the total cost for the buyer, making it easy to see at a glance the profitability zones and risk areas.
Occupancy discount in occupied life annuity: a lever often poorly calibrated in spreadsheets
In occupied life annuity, the seller retains a right of use and habitation (DUH). The value of this right is deducted from the market value to determine the base for calculating the annuity. Field feedback varies on the valuation method: some professionals apply a flat percentage, while others capitalize a fictitious rent over the estimated duration of occupancy.
The method of capitalizing a fictitious rent provides more accurate results than a fixed percentage, as it takes into account local rental pressure. In the spreadsheet, this means a dedicated cell for the monthly market rent, another for the estimated duration of occupancy, and a formula that calculates the present value of these rents.
A common pitfall in downloaded Excel simulators: the discount is fixed at a single rate, unrelated to the actual rental market. Changing this parameter in an alternative scenario can sometimes significantly alter the annuity, much more than the initial payment.
Limits of an Excel simulator against the reality of a life annuity
A spreadsheet models hypotheses, not certainties. Life expectancy remains a statistical average that does not predict the actual duration of the contract. The mortality tables used by notaries and life annuity professionals are regularly updated, and a deviation of a few years on this data is enough to reverse the profitability of a scenario.
The annual revaluation of the annuity constitutes another blind spot. Most simulators apply a fixed rate, while contractual indexing often follows an index (consumer price index, reference rent index). Integrating a variable rate in Excel is possible with conditional formulas, but it complicates the maintenance of the file.
What a spreadsheet cannot replace
- Notarial expertise in drafting clauses (reversibility of the annuity, resolutory clause, mortgage guarantee).
- The contradictory assessment of the market value, which conditions all the upstream calculations.
- The human diagnosis on the coherence between the seller’s profile (age, tax situation, cash needs) and the selected parameters.
The Excel simulator is used to sort hypotheses, not to validate a sale price. It allows eliminating manifestly unbalanced initial payment/annuity combinations and arriving before a professional with precise questions rather than a blank page. This distinction remains the best safeguard against calibration errors that propagate from one cell to another without being noticed.
