Insurance appraisals usually state replacement value rather than purchase cost, rounded to a clean figure. CEILING rounds the marked-up value up to the next $50, and multiplying by the premium rate estimates the annual cost to insure the piece.
A watch appraised at $5,150 with a 15% markup is valued at $5,950, which costs $89.25 a year at a 1.5% premium.
The example
Three worked examples. Change any yellow input in the practice workbook and the results recalculate.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Case | Cost | Markup | Premium | Replacement | Premium |
| 2 | Ring | $2,400 | 20.0% | 1.5% | $2,900 | $43.50 |
| 3 | Watch | $5,150 | 15.0% | 1.5% | $5,950 | $89.25 |
| 4 | Necklace | $860 | 25.0% | 2.0% | $1,100 | $22.00 |
The formula
Here is the formula, built directly from the inputs:
How it works
Here is the logic, one piece at a time:
B2*(1+C2)adds the replacement markup.CEILING(...,50)rounds up to the next $50.E2*D2applies the premium rate.- Both results update when the markup changes.
Keep every number in its own input cell so the formula never needs editing.
Try it: interactive demo
Enter the values below.
Variations
Nearest $100
Round to hundreds.
Monthly premium
Spread across twelve months.
Pitfalls & errors
Replacement values must come from a qualified appraiser; this only formats the figure.
CEILING needs a positive significance of 50 or more for these amounts.
Re-appraise every few years when metal prices move.
Practice workbook
Frequently asked questions
Why round up?
Is this insurance advice?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class