Title Insurance Premium Calculation

[asharma1180]

Hello

Can anyone help me with an excel formula to calculate title insurance premiums on Excel spreadsheets for Florida.

The rates are as follow:
up to 100,000 - 5.75
Over 100,000 up to 1,000,000 - 5.00
Over 1,000,000 up to 5,000,000 - 2.50

Thank you,

 

[iggydarsa]

something like this?

=IF(A1<=100000, 5.75, IF(A1<=1000000, 5, IF(A1<=5000000, 2.5, 0)))

 

[asharma1180]

no this does not - I am looking to calculate the premium with a loan amount and then a owners premium - as it is calculated on a CD

 

[asharma1180]

so lets say I have a loan amount 334112.00 and I have to calculate the loan premium and the primary calculation and owners premium. On a CD if there is a loan then the full amount of the premium is applied based on the loan amount and balance used as the owners premium

 

[Joe4]

so lets say I have a loan amount 334112.00 and I have to calculate the loan premium and the primary calculation and owners premium. On a CD if there is a loan then the full amount of the premium is applied based on the loan amount and balance used as the owners premium

So show us the amount you expect to see for a loan of 334112.00 (it is not clear to me whether those numbers of 5.75, 5.00, and 2.50 are flat dollar amounts or percentages).
And what happens to amounts over 5,000,000?

Don't assume that we know are familiar with Title Insurance Premium Calculations, so show us how they work, through an example.

 

[Joe4]

This is my best guess regarding what you are after.

If it is a matter of:
- charging 5.75% on the first 100,000
- charging 5.00% on the next 900,000 (from 100,000 to 1,000,000)
- charging 0.25% on the next 4,000,000 (from 1,000,000 to 5,000,000)
- charging 0% on anything over 5,000,000

Then you could use a formula like this for an amount in cell A1:

=(MIN(MAX(A1,0),100000)*0.0575)+(MIN(MAX(A1-100000,0),900000)*0.05)+(MIN(MAX(A1-1000000,0),4000000)*0.025)

 

[asharma1180]

I apologize -

So for a loan amount of $335,854.00 the premium on the CD comes to $1,754.50 ------ with a sale price of $342,050.00 the owners premium calculates to $306.00

The Title Insurance rates in Florida are as follow:
up to 100,000 (of loan amount or sales price) - $5.75/$1000
Over 100,000 up to 1,000,000(of loan amount or sales price) - $5.00/$1000
Over 1,000,000 up to 5,000,000(of loan amount or sales price) - $2.50/$1000

As an example - an $100,000 loan amount or sales price would have a premium of $575.00

 

[Joe4]

So for a loan amount of $335,854.00 the premium on the CD comes to $1,754.50 ------ with a sale price of $342,050.00 the owners premium calculates to $306.00

Can you break it down, and walk through this example step-by-step to show us exactly how you arrived at those specific values for this example?

 

[asharma1180]

Can you break it down, and walk through this example step-by-step to show us exactly how you arrived at those specific values for this example?

Our system closing software calculates it automatically but here is how it is calculated mannualy:

335,854.00 is rounded up to 335,900
take the first 100,000 of above amount = 575.00
then balance 235,900 *5.00/1000 = 1179.50
so the total of 2 numbers above is = 1754.50

but then on the Closing Disclosure the Owners Policy premium comes to 306.00

Plus endorsement of 90.00 and 175.45 = 265.45

 

[Joe4]

OK, so there was the key detail you were leaving out.

In your original post, you said:

The rates are as follow:
up to 100,000 - 5.75
Over 100,000 up to 1,000,000 - 5.00
Over 1,000,000 up to 5,000,000 - 2.50

And then in your second post, you said:

The Title Insurance rates in Florida are as follow:
up to 100,000 (of loan amount or sales price) - $5.75/$1000
Over 100,000 up to 1,000,000(of loan amount or sales price) - $5.00/$1000
Over 1,000,000 up to 5,000,000(of loan amount or sales price) - $2.50/$1000

So the amounts of 5.75, 5.00. and 2.50 are not percentages, they are per $1000 (which you did not mention originally).
So they actually are a kind of percentage, just ten times lower, i.e. 0.575%, 0.5%, and 0.25%.

Then you would just need to take the formula I gave you and add an extra 0 before all those factors, i.e.

=(MIN(MAX(A1,0),100000)*0.00575)+(MIN(MAX(A1-100000,0),900000)*0.005)+(MIN(MAX(A1-1000000,0),4000000)*0.0025)