Amortization - Restructure

[drake777]

I'm currently recalculate loan amortization (fixed installment) for grace period.
I'm having difficulties to determine whether the logic or the method is right
Detail problem is in the file.

Please help

Thanks before

[joeu2004]

You restructure the loan with the same interest rate and monthly payment, but longer loan term.

This is after a period of interest-only payments.

Your mistake is in thinking that total interest (N7) remains the same. Obviously, more interest is paid due to the interest-only period and the additional periods to extend the loan term.

See the highlighted cells in the "restruct" worksheet in the attached file, specifically: Q4, N7, M17, and N17:N24. The changes in M17 and N17 are cosmetic.

BTW, I deprecate the use of CUMIPMT in D7. If D5 were 1 (beginning payments), CUMIPMT would return an incorrect amount.

The formula in D5 could be simply =SUM(D10:D21). Alternatively, =D6*D4-D2 .

[joeu2004]

PS....

I deprecate the use of CUMIPMT in D7. If D5 were 1 (beginning payments), CUMIPMT would return an incorrect amount.

In fact, I avoid the use of CUMPRINC, IPMT and PPMT as well.

All of those functions were "contributed" functions originally; part of the Analysis Toolpak in Excel 2003 (and perhaps earlier). Typically, that means they were not written by MSFT engineers. At the very least, it means their implementation was not vetted and tested as thoroughly as "supported" functions.

All of those functions can be calculated using the PV and FV functions. Obviously, they were not used for the implementation of CUMIPMT and CUMPRINC, which return different (incorrect, IMHO) results for type=1 (beginning payments). Probably not for IPMT and PPMT, too. But I don't remember, off-hand.

Moreover, CUMIPMT et al do not follow Excel's standard for signed cash flows.

And using PV and FV formulas directly provides the opportunity to customize the payment (e.g. rounding). CUMIPMT et al calculate the payment internally, presumably using PMT.

[joeu2004]

Errata....

Your mistake is in thinking that total interest (N7) remains the same.
Obviously, more interest is paid due to the interest-only period and the additional periods to extend the loan term.

Sorry, but my previous suggestion is probably not the answer you are looking for.

I had changed total interest in the original loan calculation (N7) so it includes the additional interest paid during the interest-only period.

That concomitantly changed the outstanding interest for payments before the interest-only periodic (Q10:Q13).

Presumably, you would not know that before you suspended full payments. So you probably do not want the amortization schedule to reflect a difference for those earlier periods.

Since it appears that you simply want to suspend the original, keeping the "restructured loan" terms the same as the original loan except for the terminal date (actual number of periods), I presume that you also want to "suspend" the outstanding interest, so that it continues to reflect the remaining interest to be paid after the interest-only period.

To that end, change the formula in column Q (at least starting with Q14) as follows:

=IF(O14=0, Q13, Q13-N14)

You might also recalculate a total interest for the restructure loan in Q7 as follows:

=SUM(N10:N24)

-----

PS.... If this is a class assignment, forgive me for taking it too seriously.

It is admirable that the lender is willing to "restructure" (suspend) the loan for 3 months, in deference to the COVID-19 crisis.

But with the planned "restructure", the lender still earns 11% interest over the adjusted life of the loan (15 months). And in fact, the lender earns 93,329 more interest.

As gracious as the "grace period" seems, it is unethical, IMHO, to profit more from the COVID-19 crisis?

Meanwhile, the borrower must begin the original installment amount immediately after the 3-month "grace period". In reality, that might be difficult for the borrower.

I have amended the attached Excel file with an alternative restructured loan. See columns T:Y.

The same "interest-only" amount is still paid to the lender. And the loan is still extended by 3 months.

But the "interest-only" amount (31,110) is applied to the principal. In effect, the loan becomes a zero-interest loan during the grace period, but the borrower is required to make installments of the same amount.

And the post-grace period payment is recalculated based on the outstanding balance (reduced by 3 principal-only payments) and the original 11% interest rate for the remainder (8 months) of the same adjusted loan term.

The result is: the borrower pays less interest over the life of the loan.

Conversely, the lender receives less interest; but only 3891 less (299,009 instead of 302,900), a loss of 1.28% over 18 15 months. And the IRR is 8.0438% instead of 11.0000%.

Something to consider?

[drake777]

Thank you joeu2004 for the solution, but allow me to ask further question.
How about if no installment were paid during 3 months grace period, is it possible to expect the IRR remain the same as interest during payment holiday?
How to make it fair for both lender and borrower?

I attached the excel, feel free to point the mistakes of my formula or logic.

Thank you

[joeu2004]

How about if no installment were paid during 3 months grace period, is it possible to expect the IRR remain the same as interest during payment holiday? How to make it fair for both lender and borrower?

That depends on your definition of "fair". IMHO, we cannot make it "fair" for both, if "fair" means the same IRR (11%) __and__ the same (or lower) installments and total interest.

It also depends on what you intend to do with unpaid interest during the grace period: forgive the amount; or accumulate it, increasing the initially outstanding balance after the grace period.

I assume that you forgive the amount.

Review the formulas in the "restruct (new)" worksheet in the attached Excel file, notably columns M:S, W:AC and AG:AM.

I have made a number of minor improvements; too many to note here.

Also see the "PS" in post #4 and re-download the modified Excel file attachment, notably columns T:Y in the "restruct" worksheet.

In a nutshell:

1. I believe that "fair" for the borrower means: the same installment (or less) after the grace period. Consequently, I believe the lender must accept a lower IRR overall.

2. If the lender wants the same 11% IRR overall, I believe that requires a higher interest rate and larger installment after the grace period.

For #2, I was unable to determine the higher rate with a formula. I was forced to use Solver. The Solver set-up is:

Set Objective cell: AM9
To Value of: 11%
By changing cells: AK3

[drake777]

Again, thank you so much joeu2004 for the detail explanation and point of view from lender and borrower.
This is no class , i'm currently working on it in my real job.

I'm interested in what you said that:
"But with the planned "restructure", the lender still earns 11% interest over the adjusted life of the loan (15 months). And in fact, the lender earns 93,329 more interest."

If outside the ethical or unethical values of COVID-19 background, don't you think many lender will considered it fair enough if using the concept of present value? The 93,329 more interest is compensation for the delayed payment.

I really don't know whether there is some international / national regulation for this thing -> at least to accommodate the fair term for both parties.

[joeu2004]

If outside the ethical or unethical values of COVID-19 background, don't you think many lender will considered it fair enough if using the concept of present value? The 93,329 more interest is compensation for the delayed payment.

I hope the Excel formulas prove useful to you.

As for the "ethics" and interpretation of regulations, that is subjective and beyond the scope of this forum. I already expressed what "I think". And all of my comments were "inside" the COVID-19 context. I think I made that clear.

(In your original Excel file, you wrote: ``The purpose is only giving grace period during pandemic Covid-19``.)

The purpose of my comments was not to start an ethics discussion, but to give context and motivation for the alternative restructuring that I presented. I simply wanted to demonstrate yet-another way to offer a payment plan that was even more "fair" (IMHO) to the borrower under the circumstances.

What is "fair" outside these circumstances is another matter altogether. In the US, that is controlled by usury and general contract law, which often leaves much to (judicial) interpretation. I know nothing about Jakarta; nor is any of that relevant to this thread, IMHO.

For example, one of my Excel calculations resulted in an interest rate of 18.50%, 12,264 more in monthly payments, and 98,114 more in total payments. That does not mean that is legal or ethical. I was merely answering your question, to wit: what would it take to achieve a particular IRR?

[joeu2004]

one of my Excel calculations resulted in an interest rate of 18.50%, 12,264 more in monthly payments, and 98,114 more in total payments. That does not mean that is legal or ethical. I was merely answering your question, to wit: what would it take to achieve a particular IRR?

I might have an idea for a more equitable compromise. I don't have time to work on it now. If you're interested, check back tomorrow.

[drake777]

Thank you so much for the Excel joeu2004

I'm very interested and will wait for your more equitable compromise

[joeu2004]

Sorry for the delay....

For a restructured loan with zero payments, I wrote: it ``depends on what you intend to do with unpaid interest during the grace period: forgive the amount; or accumulate it, increasing the initially outstanding balance after the grace period``.

If interest is forgiven (ignored), we must charge 18.4964% interest on the remaining balance in order to achieve the goal of 11.0000% IRR.

That also results in increased monthly payments of 454,173 after the grace period.

(Refer to the table in columns AG:AM in the "restruct (new)" worksheet in the attached Excel file.)

-----

Alternatively, we might accumulate the unpaid compounded interest and add it to the remaining balance.

That allows us to continue to charge 11.0000% on the remaining balance and to achieve the goal of 11.0000% IRR.

The table below demonstrates the results of the calculations. See the "restruct (new)" worksheet in the attached Excel file for formulas.

	AP	AQ	AR	AS	AT	AU	AV	AW
1						RESTRUCTURED
2		Loan	5,000,000			3,487,970
3		Interest	11.0000%			11.0000%
4		Months	12			8
5		Adv/Arr	0			0
6		Installment	441,908			454,173
7		Interest	302,900			401,014
8								IRR
9	No	Installment	Accrued Interest	Paid Principal	Outstanding Principal	Outstanding Interest		11.0000%
10					5,000,000	302,900
11	1	441,908	45,833	396,075	4,603,925	257,066		(4,603,925)
12	2	441,908	42,203	399,706	4,204,219	214,864		441,908
13	3	441,908	38,539	403,370	3,800,850	176,325		441,908
14	4	441,908	34,841	407,067	3,393,783	141,484		441,908
15	5	0	31,110	0	3,424,892	208,488		0
16	6	0	31,395	0	3,456,287	177,093		0
17	7	0	31,683	0	3,487,970	145,410		0
18	8	454,173	31,973	422,199	3,065,770	113,437		454,173
19	9	454,173	28,103	426,070	2,639,701	85,334		454,173
20	10	454,173	24,197	429,975	2,209,726	61,137		454,173
21	11	454,173	20,256	433,917	1,775,809	40,881		454,173
22	12	454,173	16,278	437,894	1,337,915	24,603		454,173
23	13	454,173	12,264	441,908	896,006	12,339		454,173
24	14	454,173	8,213	445,959	450,047	4,125		454,173
25	15	454,173	4,125	450,047	0	0		454,173

Note that the accrued (not paid) interest (AR15:AR17) increases due to compounding, and it is added to outstanding principal (AT15:AT17).

Also note that the monthly payments after the grace period are 454,173, the same(!) as if accrued interest is forgiven. (See the "Aside" below.)

-----

If the larger monthly payments are not acceptable to the borrower, and if the lender is amenable, we can extend the term of the loan further in order to reduce the monthly payments at 11.0000% interest.

To keep the original monthly payments, the minimum number of periods after the grace period (BE4) can be calculated using the Excel NPER function.

The following table demonstrates the results of the calculations.

	AZ	BA	BB	BC	BD	BE	BF	BG
1						RESTRUCTURED
2		Loan	5,000,000			3,487,970
3		Interest	11.0000%			11.0000%
4		Months	12			8.2306
5		Adv/Arr	0			0
6		Installment	441,908			441,908
7		Interest	302,900			405,148
8								IRR
9	No	Installment	Accrued Interest	Paid Principal	Outstanding Principal	Outstanding Interest		11.0000%
10					5,000,000	302,900
11	1	441,908	45,833	396,075	4,603,925	257,066		(4,603,925)
12	2	441,908	42,203	399,706	4,204,219	214,864		441,908
13	3	441,908	38,539	403,370	3,800,850	176,325		441,908
14	4	441,908	34,841	407,067	3,393,783	141,484		441,908
15	5	0	31,110	0	3,424,892	212,623		0
16	6	0	31,395	0	3,456,287	181,228		0
17	7	0	31,683	0	3,487,970	149,545		0
18	8	441,908	31,973	409,935	3,078,035	117,572		441,908
19	9	441,908	28,215	413,693	2,664,342	89,357		441,908
20	10	441,908	24,423	417,485	2,246,857	64,934		441,908
21	11	441,908	20,596	421,312	1,825,544	44,338		441,908
22	12	441,908	16,734	425,174	1,400,370	27,603		441,908
23	13	441,908	12,837	429,072	971,299	14,767		441,908
24	14	441,908	8,904	433,005	538,294	5,863		441,908
25	15	441,908	4,934	436,974	101,320	929		441,908
26	16	102,249	929	101,320	0	0		102,249

In that example, only one more "odd payment" (BA26) is required to accommodate the remaining balance plus interest.

Alternatively, we could treat the remaining balance after the regular payments (BD25) as a "balloon payment", and add it to the last regular payment in BG25 for the purpose of calculating the IRR.

Moreover, if the lender is amenable, we can reduce the monthly payments even more by extending the loan term further arbitrarily.

For example, if we extend the loan to 18 months (11 months after the grace period), the payments after the grace period are 334,793, less than payments for the original loan.

In all cases, the IRR is the same 11.0000% (within the limitations of the Excel IRR approximation).

See the "restruct (new") worksheet in the attached Excel file for formulas, including the 18-month example in columns BJ:BQ.

The Excel implementation of that example also demonstrates how to generalize the formulas to allow for experimenting with other loan term extensions. The formulas in BO7 (total interest) and BQ9 (IRR) would need to be changed if you want to extend the loan term beyond 24 months.


-----

Aside....

Note, again, that the monthly payment is the same whether we forgive the interest or accumulate the compounded interest during the 3 months of zero payments ("grace period").

In the case of forgiving the interest, previously I had used Solver (or we could use Goal Seek) to estimate the interest rate on the remaining balance after the grace period.

Instead, now we can calculate the monthly payment directly using the outstanding balance before the grace period plus compounded interest, then calculate the estimated interest rate directly using the Excel RATE function.

That is demonstrated in AK3 and AK6 in the "restruct (new)" worksheet in the attached Excel file.

[drake777]

Sorry for the late reply. You are very helpful. The excel and knowledge is useful a lot.
I thank you for that