Data Choices

We compile. You decide.

Need Marketing Expertise?

See the video! Click the camera!

Loan Amortization for Real Estate Professionals and the Amortization Formula

At its core, the loan amortization formula is pretty complex. Because of this many real estate professionals simply refer questions about monthly payments to the lending institution. While this can be a good idea since each institution will use its own chart to compute the exact figure, a better understanding of loan amortization can help the real estate agent understand and explain this concept to clients so they have a better idea of how much house they can afford. Fortunately for the real estate professional, Microsoft Excel makes the process simple!

Let’s step through the process line by line, and then show you how you can achieve the same results easily by using built in Excel functions!

There are several things we need to know first:

The Principal (this is the maximum amount we can afford based upon a specific monthly payment, so let’s leave it blank for now)

The annual percentage rate (only we really want to know APR/12 for the monthly rate for this example)

The length of the loan (again in months with this example)

And finally the monthly payment we can afford (so let’s enter $500)

Now if we were to do this manually our formula would look like this:

As with any complex formula within Excel, it is usually best to break it down into steps.

Here we can see that we wish to add the rate to itself, but only after that rate has been divided by the formula in the denominator. So let’s make step one the denominator:

Step One: ((1+rate)^mos)-1

Now if the APR is 10% we need .10/12 or .008333 then we have ((1+.008333)^60)-1 = .6453, where mos is the number of months of the loan (60)

Step Two: rate/step one answer is .00833/.6453 = .0129

Step Three: rate + step two answer is .008333+.0129 = .0212

Step Four: Answer from step three times principal amount (let’s say $20,000 for right now) .0212 x 20000 = $424.94

And yes, just like Excel does, we rounded off our answers but used the actual values in the calculations for the more specific answer of $424.94 in monthly payments.

Now if we had these steps and formulas laid out correctly in Excel we can use Goal Seek to specify a monthly payment of $500 and determine the maximum loan we can obtain. However we will now show you here, how you can do this much easier!

Open up Excel, and type in the following formula:

=PMT(0.1/12,60,-20000)

This isn't really the formula we want, but let’s take a look at it anyway's. It tells Excel to use its own built in payment formula with a rate of .10/12 or .08333, a length of loan of sixty months and a loan amount of $20,000. Excel returns ($424.94), the same amount we calculated above. But now, let’s use Goal Seek to determine the maximum amount of the loan that we can afford based on five hundred dollars a month. In order to do this we need to alter the above formula and make references to a specific cell instead of hard, or fixed amounts. Lets make a worksheet that looks like this:

Where APR/12 is again the monthly figure that we need and MOS represents the sixty months of the loan. In the Payment cell (B5) we enter:

=PMT(B3,B4,B6)

The first cell, B3, is the rate, and as we mentioned before we need a monthly rate.

The second cell, B4, Excel refers to as nper. nper simply refers to the number of payments over the life of the loan. Cell B6, or pv, means present value of that loan, often called the principal amount at the start of the loan. Of course that amount, reflected in cell B6, is currently blank. But if we put $20,000 into cell B6, we will see that Excel will return ($424.94) in cell B5.

Instead lets make the active cell B5 and go into TOOLS and GOAL SEEK.

Let’s click in the To Value section and type in -500(the maximum monthly payment you want to make). Then lets click in the section labeled By Changing Cell and click on cell B6.

Goal Seek finds a solution and changes the value in cell B5 to ($500) and we see in cell B6 that the maximum loan we can then afford based on $500 per month is $23,532.68!

You may have also noted that there are two values that Excel did not require you to enter, fv and type. FV is future value, so if you wanted to have the loan end at some amount other than zero (completely paid off) then you could do so. Excel uses the default of zero unless you specify something else.

Type simply refers to whether payments are made at the beginning of the period or the end, with the default being the end. If you wish to change this to the beginning of the period you would enter a value of 1.

There are four important concepts that we hope you take away from this:

1. When dealing with complex formulas, break them down into steps
2. Whenever possible, have formulas refer to cells that contain a value, instead of fixed values, so tools like Goal Seek and Solver can interact with the formula
3. Make sure your formula values are consistent. Don’t use an annual percentage rate and then refer to payments in terms of months.

4. Leave the actual numbers up to the lending institution. Let the lending institution of choice come up with the estimated monthly payments. But you can use Excel to check their figures and question numbers that seem far off base.

Data Choices Products and Services

The information provided by these calculators is for illustrative purposes only. The default figures shown are hypothetical and may not be applicable to your individual situation. Be sure to consult a financial professional prior to relying on the results. The calculated results are intended for illustrative purposes only and accuracy is not guaranteed.

The information on this page and all linked pages within the www.datachoices.com domain are copyright Data Choices LLC 2007/2008 and/or its affiliates and may not be reproduced without written permission. All rights reserved. Data may be preliminary figures or theorized projections and are not guaranteed accurate. See cited sources for data updates and corrections. Opinions stated throughout the www.datachoices.com domain and all subdomains are opinions rendered for entertainment or educational value and are only opinions. Data Choices LLC is not responsible for content contained on sites located outside the datachoices.com domain, even if our site provides informational links to another domain. Some services may be provided by high quality independent contractors.Use of this site constitutes your agreement to our Terms of Use. If you do not agree do not use this site! Privacy Policy. Terms of Use. Refund Policy. Code of Ethics