IF - Excel formula to compute real estate title insurance.

J

johnnybob

New Member
Joined
Jan 12, 2011
Messages
6
I need help with an IF - Excel formula to compute real estate title insurance.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I got this formula below from a previous question that works for one specific entry:
<o:p></o:p>
=IF(A1<100000,INT(A1/1000)*4,400+INT((A1-100000)/1000)*3.25) provided by paddyd,
<o:p></o:p>
However I need additional help when it comes to different costs per $1000.00.
<o:p></o:p>
Current Premium costs per $1000.00
Up to $150,000 - $6.61 per 1M – with the above it works afterwards convoluted,
$150,000 to $250,000 add $5.18 per 1M
$250,000 to $500,000 add $4.03 per 1M
$500,000 to $10,000,000 add $3.16 per 1M
Over $10,000,000 add $2.59 per 1M
<o:p></o:p>
Example –
<o:p></o:p>
Let's say the purchase price is $425,000<o:p></o:p>
<o:p></o:p>
The first $150,000 x 6.61 = 991.50<o:p></o:p>
The next 100,000 x 5.18 = 518.00<o:p></o:p>
Then 175,000 x 4.03 = 705.25<o:p></o:p>
$425,000 2214.75 total premium
<o:p></o:p>
In advance thank you if anyone can help me…johnnybob.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Try this:

=SUMPRODUCT((A1>{0;150;250;500;10000}*1000) * (A1-{0;150;250;500;10000}*1000) * ({6.61;-1.43;-1.15;-0.87;-0.57}))/1000

{0;150;250;500;10000} are the breakpoints in $K

{6.61;-1.43;-1.15;-0.87;-0.57} is the initial rate and the deltas to higher rates

Or, if you put the breakpoints and rates in a table,

Code: 
      -----A----- ---B--- ----------------------------C----------------------------
  1                                                                                
  2            0     6.61                                                          
  3      150,000     5.18                                                          
  4      250,000     4.03                                                          
  5      500,000     3.16                                                          
  6   10,000,000     2.59                                                          
  7                                                                                
  8      425,000  2214.75 B8: =SUMPRODUCT((A8>A2:A6)*(A8-A2:A6)*(B2:B6-B1:B5))/1000
 
Last edited:
Upvote 0
=if(a1<=150000,a1*0.00661,if(a1<=250000,(a1-150000)*0.00518+991.5,if(a1<=500000,(a1-250000)*0.00403+1509.5,if(a1<=10000000,(a1-500000)*0.00316+2517,if(a1>10000000,(a1-10000000)*0.00259+32537)))))
 
Upvote 0
Welcome to the MrExcel board!

Another option would be to get the lookup table (columns D:F below) to do a bit more of the work.

I've shown a few sample calculations in columns A:B.

Formulas in B2 and F3 copied dowm.

Excel Workbook
ABCDEF
1PricePremium
2425,0002,214.7506.61
3100,000661.00150,0005.18991.50
4500,0002,517.00250,0004.031,509.50
520,000,00058,437.00500,0003.162,517.00
680,000528.8010,000,0002.5932,537.00
7
Premium Calc
 
Upvote 0
Thank you very much - grateful for your feedback and quick reply!

Peter_SSs said:
Welcome to the MrExcel board!

Another option would be to get the lookup table (columns D:F below) to do a bit more of the work.

I've shown a few sample calculations in columns A:B.

Formulas in B2 and F3 copied dowm.

Excel Workbook
ABCDEF
1PricePremium
2425,0002,214.7506.61
3100,000661.00150,0005.18991.50
4500,0002,517.00250,0004.031,509.50
520,000,00058,437.00500,0003.162,517.00
680,000528.8010,000,0002.5932,537.00
7
Premium Calc
Click to expand...
 
Upvote 0
Thank you very much - grateful for your feedback and quick reply! This also works as desired...

Cloverken said:
=if(a1<=150000,a1*0.00661,if(a1<=250000,(a1-150000)*0.00518+991.5,if(a1<=500000,(a1-250000)*0.00403+1509.5,if(a1<=10000000,(a1-500000)*0.00316+2517,if(a1>10000000,(a1-10000000)*0.00259+32537)))))
Click to expand...
 
Upvote 0
Thank you very much - grateful for your feedback and quick reply!

shg4421 said:
Try this:

=SUMPRODUCT((A1>{0;150;250;500;10000}*1000) * (A1-{0;150;250;500;10000}*1000) * ({6.61;-1.43;-1.15;-0.87;-0.57}))/1000

{0;150;250;500;10000} are the breakpoints in $K

{6.61;-1.43;-1.15;-0.87;-0.57} is the initial rate and the deltas to higher rates

Or, if you put the breakpoints and rates in a table,

Code: 
      -----A----- ---B--- ----------------------------C----------------------------
  1                                                                                
  2            0     6.61                                                          
  3      150,000     5.18                                                          
  4      250,000     4.03                                                          
  5      500,000     3.16                                                          
  6   10,000,000     2.59                                                          
  7                                                                                
  8      425,000  2214.75 B8: =SUMPRODUCT((A8>A2:A6)*(A8-A2:A6)*(B2:B6-B1:B5))/1000
Click to expand...
 
Upvote 0
Cloverken said:
=if(a1<=150000,a1*0.00661,if(a1<=250000,(a1-150000)*0.00518+991.5,if(a1<=500000,(a1-250000)*0.00403+1509.5,if(a1<=10000000,(a1-500000)*0.00316+2517,if(a1>10000000,(a1-10000000)*0.00259+32537)))))
Click to expand...

This formula you provided works perfectly, however, the title company wants to reflect an amount if the seller has lived in the home for 10 years or less. A discount prevails called a “Title Reissue Discount”.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
The discount table is as follows:
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 163.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=219><TBODY><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="0"> $ - <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="4.0250000000000004"> $ 4.025 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="150000"> $ 150,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="3.15"> $ 3.15 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="250000"> $ 250,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="2.45"> $ 2.45 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="500000"> $ 500,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="1.925"> $ 1.93 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="10000000"> $10,000,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="1.575"> $ 1.58 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.95pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="200000"> $ 200,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="761.25" x:fmla="=SUMPRODUCT((A8>A2:A6)*(A8-A2:A6)*(B2:B6-B1:B5))/1000"> $ 761.25 <o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
The formula I used is =SUMPRODUCT((A8>A2:A6)*(A8-A2:A6)*(B2:B6-B1:B5))/1000 and or =if(a1<=150000,a1*0.00661,if(a1<=250000,(a1-150000)*0.00518+991.5,if(a1<=500000,(a1-250000)*0.00403+1509.5,if(a1<=10000000,(a1-500000)*0.00316+2517,if(a1>10000000,(a1-10000000)*0.00259+32537)))))<o:p></o:p>
<o:p> </o:p>
However, the original table (standard) you all provided is based on this table below:
<o:p> </o:p>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 163.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=219><TBODY><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap>$ - <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="6.61"> $ 6.61 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="150000"> $ 150,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="5.18"> $ 5.18 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="250000"> $ 250,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="4.03"> $ 4.03 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="500000"> $ 500,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="3.16"> $ 3.16 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="10000000"> $10,000,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="2.59"> $ 2.59 <o:p></o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap x:num="200000"> Total Premium
$ 200,000.00 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap x:num="1250.5"> $ 1,250.50
</TD></TR><TR style="HEIGHT: 13.1pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 89.4pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=119 noWrap><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 74.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=99 noWrap><o:p> </o:p>
</TD></TR></TBODY></TABLE>
Same formula as above, however I must now integrate this ten year rule some how some way.
<o:p> </o:p>
For an example if the home sells for $200,000.00, however the seller paid title insurance for $150,000 5 years previously, therefore the first $150,000.00 premium rate is @ $4.025 (from the 10 year or less rate above) and the next $50,000.00 premium rate is @ $5.18 from the standard rate.
<o:p> </o:p>
Some title companies provide the regular rate and in a separate cell show a discount total.
<o:p> </o:p>
Example a home selling for $ 200,000 the premium is $1,250.50 and the discount (ten year rule) net is $1121.75 or $128.75 discount. I prefer showing a discount if applicable.

As always your help is appreciated.

johnnybob
 
Upvote 0
I have a similar question. I'm trying to do the same thing, but in Texas, I don't know the formula, just the values. I have a two column list. In column A are the purchase price values. In Column B are the fee values. I want a formula that says 'Look at the purchase price value (cell G3). If G3's value is between the values for A1 and A2, then show the value in B1; If G3's value is between the values for A2 and A3, then show the value in B2; etc. through 307 rows.

Thanks in advance. I just can't get my head wrapped around how the IF,AND, OR, and BUT formulas work...
 
Upvote 0
architechnid said:
I have a similar question. I'm trying to do the same thing, but in Texas, I don't know the formula, just the values. I have a two column list. In column A are the purchase price values. In Column B are the fee values. I want a formula that says 'Look at the purchase price value (cell G3). If G3's value is between the values for A1 and A2, then show the value in B1; If G3's value is between the values for A2 and A3, then show the value in B2; etc. through 307 rows.

Thanks in advance. I just can't get my head wrapped around how the IF,AND, OR, and BUT formulas work...
Click to expand...
Seems like you got an answer here ..

.. but please do not post the same question multiple times. Refer to #9 of the Forum Rules
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
IF - Excel formula to compute real estate title insurance.
Replies
0
Views
1K
johnnybob
J
J
  • Question Question
If Function Help for Real Estate Title Ins Calc
Replies
3
Views
678
jrobertsandco
J
M
  • Question Question
Real Estate Title Insurance Premium
Replies
1
Views
765
MorganO
MorganO

Forum statistics

Threads
1,224,524
Messages
6,179,304
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top