"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.