Repaymen of loan excel sheet with grace period etc...

[Morten]

Hi,

Can anyone help on this problem:

Trying to build a spreadsheet for repayment of a loan, would like 3 variables:

1) No of yrs for repayment
2) Possibility of a grace period/interest payments only for X yrs
3) Interest rate

For example:
Loan $100,000/serial loan
Interest rate: 4%
Repayment 10 yrs
Grace period 2 yrs (that means 2 first years only pay interest and then
subsequently you start to pay the loan down from yr 3)

Is there some formulas that can do this? I have been trying to use some of
the loan calculators that excel have as templates but they do not allow you
to have a grace period and it is an annuity loand and not serial.

Can anyone help on this?

In advance thanks.

 

[JoeU2004]

Grace period 2 yrs (that means 2 first years only pay interest and
then subsequently you start to pay the loan down from yr 3)

That's an interest-only period, not a grace period. A "grace period" is
when no payment at all is required. Interest may or may not accrue during a
grace period. But that is not relevant to your question, since you have
already said that accrued interest will be paid periodically.

Loan $100,000/serial loan
Interest rate: 4%
Repayment 10 yrs

During the interest-only period, the payments are simply the amount of
interest. For monthly payments, the fixed payment amount is:

=roundup(100000*4%/12,2)

Note: ROUNDUP ensures that the lender does not lose fractional interest.
Alternatively, the lender might round the payment differently and rely on an
uneven "catch-up" payment, either annually or at the end of the
interest-only period.

After the end of the interest-only period, of course no principal has been
paid. So the loan is simply restructured as a regular loan for the original
loan term less the interest-only period -- 8 (10-2) years in this example.

The key is: this is a serial loan. So, we cannot use any of the typical
Excel functions, e.g. PMT and NPER, since they assume an annuity loan.

With a serial loan, the periodic payment (monthly in my examples) is
variable, composed of two parts: a fixed part that pays down the principal;
and a variable part that pays the interest accrued during the period.

Ostensibly, the periodic principal part ("principalPmt") is: 100000/nper,
where "nper" is the number of payment periods, 96 (8*12) in this example.

Ostensibly, the periodic interest part is: remainingBalance*4%/12. And the
new remaining balance is: remainingBalance - principalPmt.

In real life, adjustments must be made to round the total payment and to
account for the cumulative effects of rounding by altering the last payment.

Trying to build a spreadsheet for repayment of a loan, would like 3
variables:

1) No of yrs for repayment
2) Possibility of a grace period/interest payments only for X yrs
3) Interest rate

Ignoring the real-life issues, here is a no-frill model.

B1: 100000 (loan amount)
B2: 4% (annual interest rate)
C2: =B2/12 (monthly interest rate; format as Percent with 6 dp)
B3: 120 (loan term, months)
B4: 24 (interest-only term, months)
B5: =B1*C2 (interest-only pmt; format as Number with 2 dp)
B6: =B3-B4 (remaining term; format as Number with 2 dp)
B7: =B1/B6 (principal pmt; format as Number with 2 dp)

(I assume you will want to put corresponding labels in column A.)

Columns A, B, C, D and E are the payment number, total payment, principal
payment, interest payment and remaining balance. Format columns B, C, D and
E as Number with 2 dp.

E10: =B1
A11: 1
B11: =C11+D11
C11: =$B$7
D11: =E10*$C$2
E11: =E10-C11
A12: =A11+1

Copy B11:E11 to B12:E12. Copy A12:E12 down until column A is 96.

(I assume you will want to put corresponding labels in row 9.)

After you build this loan repayment schedule, the total interest can be
computed simply by SUM(D11106). You can also compute the total interest
by:

=C2*B6*(B1-B7*(B6-1)/2)

That is: periodicRate*nper*(principal - principalPmt*(nper-1)/2).

Does this address all of your needs.

Reminder: The above does not account for real-world issues. So it might
not be appropriate for professional usage. But then again, I would say the
same for any of the Excel templates and online loan calculators.


----- original message -----

 

[Morten]

Hi,

Thank you for your help, need som additional help:

I would like to be able to change interest-only term (B4) to x, y, z months,
then the loan repayment automatically reflects this, would also like to have
the same option for the loan term. ie want to have the possiblity to change
these two variables (increase/decrase the periods) and it automatically
recalculates the payments.

In advance thanks again for all your help!

Brgds
Morten

JoeU2004 skrev:

 

[JoeU2004]

I would like to be able to change interest-only term (B4) to x, y, z
months,
then the loan repayment automatically reflects this

I believe that already exists. Simply put the number of months into B4.
But....

would also like to have the same option for the loan term these two
variables (increase/decrase the periods) and it automatically
recalculates the payments.

Use a template structure. You will need to chose a maximum for the original
loan term -- for example, 30 years. Then:

E10: =B1
A11: 1
B11: =if(A11="","",C11+D11)
C11: =if(A11="","",$B$7)
D11: =if(A11="","",E10*$C$2)
E11: =if(A11="","",E10-C11)
A12: =if(or(A11="",A11=$B$6),"",A11+1)

Copy B11:E11 to B12:E12. Copy A12:E12 down through A370:E370 (30*12
months).


I wrote previously:

After you build this loan repayment schedule, the total interest can be
computed simply by SUM(D11106). You can also compute the total
interest
by:

=C2*B6*(B1-B7*(B6-1)/2)

Compute SUM(D11370), or simply use the second formula.


----- original message -----

 

[Morten]

Hi again,

Thanks again for your help! Still have some questions/difficulties (I am not
so experienced in excel, but willing to learn):

I have now put in 360 months (B3) for loan term and 12 months (B4) for
interest only terms. In the repayment schedule, it shows principal payments
of 287 from period 1-348.

I would like to have principal payments to be 0 in the first 12 (1-12)
periods and that the total payments in period 1-12 to just only reflect the
interest payments (this means remaining balance to be the same also from
period 0-12). From period 13-360 you start to pay down on the loan.

I hope you understand my question and again thanks for all your help.

Brgds
Morten

JoeU2004 skrev:

 

[JoeU2004]

I would like to have principal payments to be 0 in the first 12 (1-12)
periods and that the total payments in period 1-12 to just only reflect
the
interest payments (this means remaining balance to be the same also from
period 0-12). From period 13-360 you start to pay down on the loan.

Okay. Again, ignoring real-world issues (viz. rounding payments), the only
formulas that need to change are:

A12: =if(or(A11="",A11=$B$3),"",A11+1)
C11: =if(A11="","",if(A11<=$B$4,0,if(A11=$B$3,E10,$B$7)))

Copy C11 down through C370 to allow for up to 30 years (30*12 months).


Errata....

My previous formulas for total interest paid neglected to account for the
interest-only period. If you use the SUM formula, the above changes will
correct my omission automatically. If you use the "algebraic" formula, it
should be changed to:

=B4*B5 + C2*B6*(B1-B7*(B6-1)/2)


Addendum #1....

It occurred to me that you probably want to have the payment due date.
Ideally, that would be in column B, next to the payment number. But to
avoid confusion with my previous formulas, I will show it in column F.

F11: 5/31/2009 (payment #1 due date)
F12:
=IF(A12="","",min(date(year($F$11),A11+month($F$11),day($F$11)),eomonth($F$11,A11)))

Copy F12 down through F370.

The MIN expression ensures that: (a) if the first due date is the last day
of the month, all due dates are the last day of their respective months; and
(b) if the first due date is 29 or later, the due date is the last day of
any month with fewer days.

If EOMONTH causes a #NAME error, and you do not want "install" (enable) the
Analysis ToolPak, you can substitute the following:

date(year($F$11),A12+month($F$11),0)

Note that sometimes I use A11+MONTH(...), and other times I use
A12+MONTH(...). This is on purpose. A11+MONTH(...) is shorthand for
A12-1+MONTH(...).


Addendum #2....

The following model incorporates those pesky real-world issues caused by the
fact that a payment must be rounded to at least the lowest coin of the realm
(assumed to be 0.01). I will recap all of the formulas, although not all
are changed.

B1: 100000 (loan amount)
B2: 4.00% (annual interest rate; format as Percent
with 2 dp)
C2: =B2/12 (monthly interest rate; format as Percent
with 6 dp)
B3: 120 (loan term, months)
B4: 24 (interest-only term, months)
B5: =round(B1*C2,2) (interest-only pmt; format as Number with 2 dp)
B6: =B3-B4 (remaining term)
B7: =round(B1/B6,2) (principal pmt; format as Number with 2 dp)

For B5, note that I replaced ROUNDUP with ROUND. It is a small price to pay
for simplicity; see the formula for D11 below. Besides, it is closer to the
non-rounding template, which ignored the real-world issues, but used
formatting to display rounded numbers. (Note that rounding due to
formatting does not change the underlying number.)

E10: =B1
A11: 1
B11: =if(A11="","",round(C11+D11,2))

C11: =if(A11="","",if(A11<=$B$4,0,if(A11=$B$3,E10,$B$7)))
D11: =if(A11="","",round(E10*$C$2,2))
E11: =if(A11="","",round(E10-C11,2))
F11: 5/31/2009
A12: =if(or(A11="",A11=$B$3,round(n(E10),2)<=0),"",A11+1)
F12:
=IF(A12="","",min(date(year($F$11),A11+month($F$11),day($F$11)),eomonth($F$11,A11)))

Copy B11:E11 to B12:E12, then copy A12:F12 down through A370:F370 to allow
for up to 30 years (30*12 months).

The use of ROUND in B1 and E11 might seem redundant, considering the use of
ROUND in B7 and D11. Nonetheless, it is prudent to round B1 and E11 in
order to avoid propagating "numerical abberations" that creep into
expressions involving numbers with decimal fractions. The "numerical
abberations" arise because of the methods that Excel uses internal, viz.
standard binary floating-point representation and arithmetic.

The third condition in A12 -- ROUND(N(E10),2)<=0, ostensibly testing the
previous balance for zero -- covers the case when the principal payment (B7)
is rounded to a larger denomination, potentially causing the loan to paid
off sooner. Some lenders round the payment to an even dollar, for example.
The N() function covers for the fact that ROUND does not tolerate a text
parameter (notably the null string, "") as some functions do :-(.

Note that due to rounding of periodic amounts, the total interest can no
longer be computed the "algebraic" formula that I posted previously and
corrected above. The SUM formula should work just fine. But again, it
would be prudent to round the result, namely: ROUND(SUM(D11370),2).

HTH.


----- original message -----

 

[JoeU2004]

Errata.... ("I will not post formulas without testing them first." --
written 1000 times ;->.)

C11: =if(A11="","",if(A11<=$B$4,0,if(A11=$B$3,E10,$B$7)))
[....]
A12: =if(or(A11="",A11=$B$3,round(n(E10),2)<=0),"",A11+1)

[to cover] the case when the principal payment (B7) is rounded to a
larger denomination, potentially causing the loan to paid off sooner.

C11: =if(A11="","",if(A11<=$B$4,0,if(or(A11=$B$3,E10<$B$7),E10,$B$7)))
A12: =if(or(A11="",A11=$B$3,round(n(E11),2)<=0),"",A11+1)


----- original message -----

 

[JoeU2004]

Errata....

=IF(A12="","",min(date(year($F$11),A11+month($F$11),day($F$11)),eomonth($F$11,A11)))
[....]
The MIN expression ensures that: (a) if the first due date is the last
day of the month, all due dates are the last day of their respective
months; and (b) if the first due date is 29 or later, the due date is the
last day of any month with fewer days.

Actually, it ensures only #b. For example, if the first due date is Apr 30,
the next due date will be Mar 30, not Mar 31.

If you want #a as well as #b, one way is:

F1: =(F11=eomonth(F11,0))
F12: =if(A12="","",if($F$1,eomonth($F$11,A11),
min(date(year($F$11),A11+month($F$11),day($F$11)),eomonth($F$11,A11))))


----- original message -----