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:

"Morten" wrote:
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(D11:D106). 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" wrote in message
...
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.

See my response to your duplicate posting in the original thread, where the
discussion belongs.


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

"Morten" wrote in message
...
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:

"Morten" wrote:
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(D11:D106). 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" wrote in message
...
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.