loan amortization loan in months

[weisse]

I do not understand accounting, however, I need to create/obtain a loan
amortization in months, not years. Is there an easy way to create or does
anyone know where I can obtain one?

 

[Niek Otten]

It's (almost) the same as in years, just use the number of months for duration and adjust the rate to a monthly rate.

Look at templates here:

http://office.microsoft.com/en-us/excel/HA010346401033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I do not understand accounting, however, I need to create/obtain a loan
| amortization in months, not years. Is there an easy way to create or does
| anyone know where I can obtain one?

 

[weisse]

It does not allow me to input the number of months (example-42 months) in the
'Loan in period of years' area. It is going to be used for is lease
agreements for equipment, therefore, we would not need up to 30 years, but
for smaller timeframes at quarter, half, three-quarter intervals, for
example, 3-1/2 years.

 

[Niek Otten]

<It does not allow me to input the number of months >

Although it doesn't say so, it actually does.
If you enter 3.5, it shows 4, but it calculates with 3.5 (I used the last template; Mortgage amortization schedule)

You can check that with Excel's PMT function:

=PMT(5%/12,42,100000) and the same data in the template

But of course you can use Excel's built-in family of financial functions too. Look here:

http://office.microsoft.com/en-us/excel/HA011117451033.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| It does not allow me to input the number of months (example-42 months) in the
| 'Loan in period of years' area. It is going to be used for is lease
| agreements for equipment, therefore, we would not need up to 30 years, but
| for smaller timeframes at quarter, half, three-quarter intervals, for
| example, 3-1/2 years.
|
| "Niek Otten" wrote:
|
| > It's (almost) the same as in years, just use the number of months for duration and adjust the rate to a monthly rate.
| >
| > Look at templates here:
| >
| > http://office.microsoft.com/en-us/excel/HA010346401033.aspx
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > |I do not understand accounting, however, I need to create/obtain a loan
| > | amortization in months, not years. Is there an easy way to create or does
| > | anyone know where I can obtain one?
| >
| >
| >

 

[weisse]

It actually has a dialog box that pops up and jingles at me 'Please enter a
whole number of years from 1 to 30'. after I hit retry, it jingles the same
dialog box at me. When I select Cancel it returns everything to 3 years with
3-year calculations.

With regard to PMT functions, I do not understand what that means as I do
not work with lengthy formulas when using Excel. The link you sent, picture
a jet flying over my head--very quickly.

Isn't there a template for this that shows loan in number of months?

 

[joeu2004]

I do not understand accounting, however, I need to create/obtain a loan
amortization in months, not years. Is there an easy way to create or does
anyone know where I can obtain one?

Well, Microsoft does have a template that might suit your needs. It is
called "Loan calculator with extra payments". Of course, you do not need to
use the extra payment feature. Do you need help loading a Microsoft template?

However, that is not an endorsement of that template. In fact, I notice
some flaws in it.

I think it is simple enough to create your own amortization schedule. Well,
perhaps "simple" is not the right word. But it is not too difficult if you
follow instructions, and you might gain some insight in the process.

Try the following bare-bones paradigm. Of course, change the numbers
accordingly.

Notes: The following assumes a US loan or similar; in particular, not a
Canadian loan. Also, I explicitly format dollar cells as Number with 2
decimal places and "1000 separator"; I explicitly format percentage cells as
Percentage with 2-4 decimal places; and I explicitly format date cells as
Date.

A1: Loan
B1: 100000
A2: Monthly Rate
B2: =6%/12
A3: Term (months)
B3: =12*30
A4: Monthly Payment
B4: =roundup(pmt(B2,B3,-B1), 2)

A6: Payment#
B6: Date
C6: Payment
D6: Interest
E6: Balance

B7: 11/6/2007
E7: =$B$1

A8: =if(n(E7)=0, "", A7+1)
B8: =if(n(E7)=0, "", if(day($B$7) > day(eomonth($B$7,row()-row($B$7))),
eomonth($B$7,row()-row($B$7)),
date(year($B$7),month($B$7)+row()-row($B$7),day($B$7))))

Note: If EOMONTH() returns #NAME, see the Help page for the remedy.

C8: =if(n(E7)=0, "", if(or(A8=$B$3, E7*(1+$B$2)<$B$4),
roundup(E7*(1+$B$2),2), $B$4))

D8: =if(n(E7)=0, "", E7*$B$2)
E8: =if(n(E7)=0, "", if(round(E7+D8-C8,2) <= 0, 0, E7+D8-C8))

Copy A8:E8 and paste into A9:E367.

HTH. Have fun!

PS: If you test the template above with a variety of "interesting"
conditions, you will begin to understand the reason for the complexity. For
example, suppose the monthly rate is 5%/12, and you round the payment (B4) up
to 10s (round(...,-1)). Notice the number of payments and the last payment
amount. For another example, choose 12/31/2007 for the initial loan date
(B7).