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

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.

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

Yes, you can create a loan repayment schedule with a grace period using Excel. Here are the steps:

1. Open a new Excel spreadsheet and create the following columns: Payment Date, Payment Amount, Interest Payment, Principal Payment, Balance.
2. Enter the loan amount in the Balance column.
3. Enter the interest rate in a cell and divide it by 12 to get the monthly interest rate. For example, if the interest rate is 4%, the monthly interest rate would be 0.00333.
4. Calculate the interest payment for the first two years by multiplying the monthly interest rate by the loan balance. Enter this amount in the Interest Payment column for the first 24 months.
5. Calculate the principal payment for the first two years by subtracting the interest payment from the monthly payment. Since there is no principal payment during the grace period, enter 0 in the Principal Payment column for the first 24 months.
6. Calculate the monthly payment using the
   Code:

   ---

   PMT

   ---

   function. The formula would be
   Code:

   ---

   =PMT(monthly interest rate, number of payments, loan amount)

   ---

   . For example, if the loan amount is $100,000, the monthly interest rate is 0.00333, and the number of payments is 120 (10 years), the formula would be
   Code:

   ---

   =PMT(0.00333, 120, 100000)

   ---

   . This will give you the monthly payment amount.
7. Calculate the principal payment for each month after the grace period by subtracting the interest payment from the monthly payment. Enter this amount in the Principal Payment column for each month after the grace period.
8. Calculate the balance for each month by subtracting the principal payment from the previous month's balance. Enter this amount in the Balance column for each month.
9. Copy the formulas in the Interest Payment, Principal Payment, and Balance columns for the entire repayment period.
10. Format the Payment Date column as a date.
11. You now have a loan repayment schedule with a grace period. You can change the variables (number of years, interest rate, grace period) to see how they affect the repayment schedule.

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

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

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

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.

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

"Morten" wrote:
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(D11:D106). You can also compute the total
interest
by:

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

Compute SUM(D11:D370), or simply use the second formula.


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

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

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:

"Morten" wrote:
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(D11:D106). You can also compute the total
interest
by:

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

Compute SUM(D11:D370), or simply use the second formula.


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

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

"Morten" wrote:
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 a

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(D11:D370),2).

HTH.


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

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

"Morten" wrote:
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(D11:D106). You can also compute the total
interest
by:

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

Compute SUM(D11:D370), or simply use the second formula.


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

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

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

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

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(D11:D370),2).

HTH.


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

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

"Morten" wrote:
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(D11:D106). You can also compute the total
interest
by:

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

Compute SUM(D11:D370), or simply use the second formula.


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

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

Errata....

I wrote:
=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 -----

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

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(D11:D370),2).

HTH.


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

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

"Morten" wrote:
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(D11:D106). You can also compute the total
interest
by:

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

Compute SUM(D11:D370), or simply use the second formula.


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