[APRA-NW] real estate and income calculation weirdness in
WealthEngine
Mildner, Chris
cmildner at clark.edu
Mon Oct 24 14:43:28 PDT 2011
This is an "in case you didn't know" email about WealthEngine real estate and income calculations. I have permission from our WealthEngine Account Representative Erin Schuessler to share this - the income formula is not proprietary to WE.
I have been validating data from my screening and have had several prospect whose real estate values in the Estimated Giving Capacity Summary far exceeded the actual value I came up with after verifying the properties listed and adding their total value. This also made the estimated income and the capacity ratings appear vastly inflated.
Erin told me that WE takes the highest amount of the sale, market and assessed values in the record. So the reason for the skewed real estate value total was that some of the properties had the exact same sale amount and date in them. That indicated that a single lot had been purchased at for that big amount and then broken up into smaller lots worth a lot less. WealthEngine had added that big sale amount over and over because it was the highest number. So now I'm looking at the real estate records more closely to make sure I catch this elsewhere.
Erin shared a way to fix the problem. To adjust the real estate total on the Match Summary Tab :
1. Click the little green circle with the plus on the Supplemental Ratings line just above the Estimated Giving Capacity Summary
2. Select Real Estate
3. Then put a minus sign and whatever amount you want to deduct from the Giving Capacity (e.g.: - 310,000)
4. Click OK to recalculate the Giving Capacity
5. The numbers in the Estimated Giving Capacity Summary will change
6. The Gift Capacity Range in the Rating Summary will change if affected
Additionally, if it says "(from Lexis Nexis)" next to Income Component in the Estimated Giving Capacity Summary, income also was calculated based on the real estate total, but the income calculation does not adjust when you change the real estate values. So that amount may need to be adjusted downward as well.
To calculate income, Erin says that they assume a loan amount of 80% of the market value and a monthly interest rate of .07/12 on a 30 year loan. They use a percentage of 1 and a multiplier of 5. Erin gave me the formulas for calculating the monthly mortgage payment and the calculated income, as follows:
Interest Rate = .07/12
Multiplier = 5
Loan Amount = 80% of Market Value
Monthly Mortgage Payment = Loan Amount*((0.07/12)/(1-(1+0.07/12)^(-30*12)))
Income Calculated = (monthly mortgage payment * multiplier) * 12
Monthly Mortgage Payment = loan amount *(monthly interest/ (1- (POWER((1+ monthly interest), (total years loan *12))) income calculated = (monthly mortgage payment * multiplier ) * 12
POWER = (10,2) = 10*10 which is 100
I thought that was way too complicated, so I calculated the percentages on several prospects and came up with income equaling around 32% of real estate holdings. I had been using 28% of the total loan amounts based on bank requirements I researched about 8 years ago, but since banks are being more stringent, perhaps 32% is better for more recent purchases.
What I have been doing to fix the income component is using the same instructions as above, except choosing Income instead of Real Estate as the component to adjust. I subtract the difference between the original WE income calculation and my new one based on the corrected real estate value total.
I hope this is helpful.
Chris Mildner, CFRE
Director of Research
Clark College Foundation
(360) 992-2909
cmildner at clark.edu<mailto:cmildner at clark.edu>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 658 bytes
Desc: image001.png
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 636 bytes
Desc: image002.png
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 485 bytes
Desc: image003.png
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.jpg
Type: image/jpeg
Size: 23189 bytes
Desc: image004.jpg
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.jpg
Type: image/jpeg
Size: 17831 bytes
Desc: image005.jpg
URL: <http://mailman13.u.washington.edu/pipermail/apra-nw/attachments/20111024/185a5cd3/attachment-0001.jpg>
More information about the APRA-NW
mailing list