How to get the final principal balance of a mortgage upon maturity?

Never mind. Figured it out after extensive googling. Hopefully this will help someone in the future.

Formula uses FV function:

- Click on fx function button and choose FV
- Rate = 0.412391547% (or select the cell this is listed in)
- Nper = 60 (or select the cell this is listed in)
- Pmt = $17,488 (or select the cell this is listed in)
- Pv = -$3,000,000 (or select the cell this is listed in) (also, make sure a negative sign is in front. doesn't have to be in the actual cell but rather in the formula)
- Type = 0 (if payment takes place at end of period. if it takes place at beginning then type = 1)

By applying the same formula to all which means for "type" I didn't differentiate between 0 and 1 specific to each mortgage. The result was that the outstanding principal balance remaining at the maturity date for each mortgage differed by a few dollars to a couple hundred dollars while some were spot on with what the amortization schedule showed in the file. It depends on the payment date I suppose, and I am not exactly sure how to fix this. But for my purposes I needed an approx. figure.

Also, some returned a negative result by a few bucks under zero. To make up for that I had the formula automatically change any negative to $0.00. I had to use the MAX function.


This is an example formula:
=MAX(0,FV(H195,J195,I195,-B195,0))

PS.... I see you posted your own "solultion" while I was composing my posting. I interpreted the problem differently than you, apparently. The answer below might still be of interest (no pun intended), in case your interpretation is incorrect. (I think it is.)

Originally Posted by megaman1978

I have a list of 200 mortgages in a spreadsheet. I have several columns of relevant info for each mortgage but the one piece of info I don't have, is the final principal balance of the mortgage when it reaches its maturity date.

I suspect this is homework, since if you had 200 loans of $3M, you would probably have professional software or a CPA that could do the calculations. But I'll play.

Originally Posted by megaman1978

I'm in Canada, where I believe our interest calculation is slightly different

Yes. The monthly rate is calculated by =RATE(6,0,-1,1+i/2), where "i" is the advertised annual rate. That is consistent with your example. I demonstrate the calculation below.

Originally Posted by megaman1978

Here is a mortgage for example:
* Mortgage # = 12345
* Original principal balance = $3,000,000
* Interest rate = 5%
* Periodic rate = 0.412391547%
* First payment = Feb. 1, 2015
* Fixed payment amount each month = $17,448.15
* Rounded fixed payment amount each month = $17,448.00
* Term period = 60 months (5 years)
* Amortization period = 300 months (25 years)
* Maturity date = Jan. 1, 2020

I assume you mean the payments are interest-only during the first "term period", and the loan is fully amortized over the remaining "amortized period". That is consistent with your example monthly payment. I demonstrate the calculation below.

In that case, the "final principal balance" should be about zero at maturity; that is, after the last payment. So I presume you want the outstanding balance before the last payment.

But keep in mind that mortgages are usually paid in arrears (end of each period); and that is the assumption implied by your example amounts. So the final payment is the outstanding balance plus interest.

The calculations are demonstrated by the following.

	A	B	C	D
1	loan	3,000,000.00
2	annl rate	5.0000%
3	mon rate	0.412391547%
4	mon pmt	17,448.00	17,448.15	unrounded
5	int-only term	60	months
6	amort term	300	months
7	int-only pmt	12,372.00	12,371.75	unrounded
8	prin #360	17,464.36
9	pmt #360	17,536.38

Please Login or Register  to view this content.

Note that the actual calculated monthly interest rate is about 0.412391546514773%. If you want to round that to 9 percentage decimal places, as it is displayed, enter the following into B3: =ROUND(RATE(6,0,-1,1+B2/2),11), since 0.412391546514773% is actually 0.00412391546514773.

Originally Posted by megaman1978

The term period of 5 years is actually principal + interest payments rather than interest only.
[....]
I needed to know the principal balance at the end of the term period only and not the amortization period

Okay. You repeatedly misstated the problem, saying "at maturity" instead of end of the term period.

Funny, I thought that might be what you intended. But surprisingly, the example payment (17,488) is the correct amount to retire a loan of $3M over 300 months, the "amortization period". That led to my interpretation that the initial "term period" must be interest-only payments.

With a payment of 17,488 during the "term period", the outstanding balance is 2,652,518.69 = FV(RATE(6,0,-1,1+5%/2),60,17488,-3000000).

If the same payment is used during the "amortization period", the loan would be retired in 239 months = ROUNDUP(NPER(RATE(6,0,-1,1+5%/2),17488,-A1),0), where A1 is the outstanding balance, instead of 300 months.

On the other hand, perhaps your intent is to calculate a different payment for the "amortization period". That would be 15,427 = ROUND(PMT(RATE(6,0,-1,1+5%/2),300,-A1),0).

PPS.... But if that is the case, I would be interested in knowing the reason for the dichotomy, and how 17,488 was chosen in the first place.

The last payment for the "amortization period" would be 15,533.92 = ROUND(FV(RATE(6,0,-1,1+5%/2),299,A6,-A1)*(1+RATE(6,0,-1,1+5%/2)),2), where A6 is the regular payment.

PS.... Of course, RATE(6,0,-1,1+5%/2) should be calculated once in some cell.

Originally Posted by megaman1978

As we speak I am currently trying to figure out the "type 0 and type 1" issue and if I can resolve that issue.

Type=1 is for "loans" where interest is paid in advance, at the beginning of each period. That is typical for leases. I have never seen a mortgage that is structured that way.

Type=0 is for loans where interest is paid in arrears, at the end of the each period. That is typical of mortgages and most other monetary loans.

Of course I just realized now that rounding the monthly payment amount changes the outcome of the outstanding principal balance after whatever term period. Rounding down means slightly less of the principal was paid after the term period. Rounding up means slightly more is paid.

If it was a big commercials mortgage of say $10,000,000 and the term and amortization were 15 or 20 years and the payment each month was supposed to be $30,000.49 but was rounded down to $30,000.00 then that extra 49 cents that's not being paid by the borrower over 15 or 20 years on such a big mortgage really makes a difference and easily results in a couple hundred dollars difference between the outstanding principal remaining if you calculate with and without a rounded down payment amount.

Many commercial lenders do rounding as opposed to residential mortgages which does count the cents.

Originally Posted by megaman1978

Perhaps there is a difference in lingo between the US and Canada for banking/lending. If a mortgage has a 5 year term on a 25 year amortization, we say the loan maturity date is 2020 (after 5 years) as opposed to saying the maturity date is 2040 when the loan fully amortizes. To be more specific I suppose we could all say the term period maturity and amortization period maturity.

Thanks for the clarification. I help a lot of people all over the world with loan calculation problems, and it is important to understand local jargon in order to minimize miscommunication.

Another Canadian loan jargon that causes much confusion is: advertised mortgage rates are "compounded semi-annually". In fact, they are compounded monthly semi-annually. That is, if "m" is the monthly rate, the advertised annual rate is ((1+m)^6 - 1)*2. Conversely, if "r" is the advertised annual rate, the monthly rate is (1+r/2)^(1/6) - 1, which we can write as RATE(6,0,-1,1+r/2) in Excel.

Originally Posted by megaman1978

I randomly checked 19 [...] and compared what result I got from the formula regarding the outstanding principal balance at term maturity vs. what it shows inside the actual file on the amortization schedule and only 11 of 19 were correct. However, the differences were only a matter of a few bucks to a couple hundred.

I suspect the difference is due to rounding the periodic interest rate. As I mentioned previously, it is not uncommon to round interest rates used in calculation to a specific number of percentage decimal places or to a multiple of a fraction, as required by law, local industry practices or lender policy.

Also note that different methods of rounding are used. The Excel ROUND function does so-called "arithmetic" rounding. VBA Round does so-called "banker's rounding". Moreover, some businesses round digit-by-digit right-to-left, whereas Excel and VBA round based on the entire digits to the right.

Originally Posted by megaman1978

It would be a whole lot easier for our discussion if I actually posted my 6 page spreadsheet on here with the 200 mortgages but it's confidential

I understand. And there might be no way to work around company policy of not sharing files outside the company, no matter how non-sensitive the material might be.

But practially speaking, there can be no harm in just sharing the numbers, eliminating any references to names and account numbers.

Alternatively, you might send the file to me directly, either using the Private Message feature of this forum or sending the file directly to me, joeu2004 "at" hotmail.com.

Or just post the numbers for a few of the 8 samples that seem incorrect.

Originally Posted by megaman1978

Of course I just realized now that rounding the monthly payment amount changes the outcome of the outstanding principal balance after whatever term period.
[....]
If it was a big commercials mortgage of say $10,000,000 and the term and amortization were 15 or 20 years and the payment each month was supposed to be $30,000.49 but was rounded down to $30,000.00 then that extra 49 cents that's not being paid by the borrower over 15 or 20 years on such a big mortgage really makes a difference

There is a rounding issue to consider, but I do not believe that is it.

The rounded payment should factor into all of the other loan calculations, as I did in my examples in postings #4 and #7.

However, there might be an issue with how the lender deals with fractional interest and principal allocated to each monthly payment.

The formulas do no month-by-month rounding, of course, since Excel cannot know the rounding policy.

But some lenders effectively add interest and subtract principal rounded or truncated to the cent to account balance, and they accumulate fractional interest separately. When the accumulated fractional interest reaches a threshold (or perhaps only periodically, e.g. quarterly), it is added to the account balance, rounded or truncated to the cent again.

However, I believe that would result in occassional differences of only a few cents, not "a few bucks to a couple hundred" that you see.

On the other hand, a rounding policy of the interest rate can make a difference, the significance of which depends on the magnitude of the principal.