Amortization Schedule

[Trish]

I am using a Amortization Schedule template from Microsoft Office online. I
am trying to change the Per Period from Monthly - Semi-Monthly etc... to days
per year. My current loan is based on a 360 day year and not a 365 which
changes the interest payment monthly. Can anyone help me.

 

[Luke M]

You'll probably want to use the EDATE formula somehow (see XL help file)
Note that you'll need to have the Analysis ToolPak Add-in turned on for this
function to work.

 

[xlm]

is there aformula that you can post, if not would you provide the link to where
you download

HTH
--
Appreciate that you click on the Yes button below if this posting was
helpful.

Thank You

cheers, francis

 

[Trish]

Thank you for the post. I have looked at the EDATE function as well as the
DAYS360 function, but I am unsure as to how I can get this into my template.
I will continue to research. Thank you.

 

[Trish]

It is a typical Amortization schedule. My porblem is that the rate per
period is taken from another cell that has a drop down box of the frequency
of payment which indicates Monthly, Semi-Monthly etc. . . What I need is my
rate per period to reflect a number that shows the monthly payment based off
a 30 day month and or a 360 day year.
Loan Amount $58,700
Annual Interest Rate 5.75%
Term of Loan in Years 3
First Payment Date 1/15/2009
Frequency of Payment Monthly

Summary

Rate (per period) 0.479%
Payment (per period) $1,130.04

Total Payments $66,118.71
Total Interest $7,418.71
Interest Savings ($2,070.17)
This is the data entry portion of the file.
If you can help that would be great!!

 

[Fred Smith]

A 360 day year simply means that all months are assumed to be the same
length. So the period that you want is Monthly. This will assume 12 months
per year, and the payment in each month is the same, regardless of how many
days in the month.

Regards,
Fred.

 

[Trish]

No it actually means that the results in a higher effective interest rate,
due to it being carried over a shorter period of time.

 

[Fred Smith]

The rates being used are correct. If the annual rate is 5.75%, then the
monthly rate is 5.75/12 = 0.479%.

You should check out the payment amount, because $1130.04/month will not pay
off the loan in 3 years. It's also unclear what "Interest Savings" is
supposed to convey.

Regards,
Fred

 

[Fred Smith]

It's the other way around, Trish. A 360-day year means the standard
amortization we've been using since day 1. 12 equal periods per year. It's
the 365-day basis which takes into account the extra days in the year, and
results in higher interest charges.

Regards,
Fred.

 

[joeu2004]

You should check out the payment amount, because
$1130.04/month will not pay off the loan in 3 years.

The numbers are consistent with the specified 3-year loan with a
balloon payment of $25,437.27. The total payment is $66,118.71 =
$25,437.27 + 36*1130.04. The total interest is $7,418.71 = 36*1130.04
- (58700 - 25437.27).

It's also unclear what "Interest Savings" is supposed to convey.

Note that "savings" is negative. Apparently, it means that the
specified loan would cost $2,070.17 more than a fully-amortizied loan
for the same term with no balloon payment.

For the latter, the payment would be $1,779.13. The total payment
would be $64,048.54 ~= 36*1779.13 [*]. The total interest would be
$5,348.54 = 64048.54 - 58700. The difference in interest for the two
loan structures is -$2,070.17 = 5348.54 - 7418.71.

[*] In calculating the interest difference, it appears that the lender
used the unrounded PMT() result. This results in an error of $0.14.



----- original posting -----

 

[joeu2004]

I am using a Amortization Schedule template from Microsoft Office
online. I am trying to change the Per Period from Monthly -
Semi-Monthly etc... to days per year. My current loan is based on
a 360 day year and not a 365 which changes the interest payment
monthly.

What I need is my rate per period to reflect a number that shows
the monthly payment based off a 30 day month and or a 360 day year.

I am confused about what you want. First you write "I am trying to
change [the template] to days per year". Then you write that you want
rate and payment to be "based off a 30 day month and or a 360 day
year". Those sound like opposite requirements to me.

Moreover, you write "my current loan is based on a 360 day year and
not a 365 year". So why would you want "days per year"?

FYI, the difference between an amortization schedule for your loan
based on 360-day and 365-day is a total of about $1.46, and about
$0.46 to $0.52 per year. So there is no "big savings" to be found in
making the change. And for IRS purposes, the year-end-totals are the
same when rounded to the dollar, as the IRS permits.

My suspicion is that your problem is: the template computes based on
a 365-day year, and you would like to change it to use a 360-day year
to match your current loan. But that is clearly not what you said at
first. Since I cannot find the template in question, I cannot resolve
this apparent contradiction.

Be that as it may, it seems easier to design a spreadsheet for your
situation and requirements than it would be to hack someone else's
template.

Put you loan parameters into columns B, namely:

B1: $58,700
B2: 5.75%
B3: 3
B4: 1130.04

Start the amortization schedule in row 6. (I am leaving room for you
to add titles, if you like.)

E6: =$B$1

A7: 1
B7: 1/15/2009
C7: =$B$4
D7: interest formula; see blow
E7: = E6 + D7 - C7

A8: =A7+1
B8: =date(year(B7),1+month(B7),day(B7))
C8: =C7
D8: copy D7
E8: copy E8

Copy A8:E8 down the remaining 34 rows.

For "interest formula", use one of the following. The first is for
standard loans based on 360-day years. The second is for loans based
on 365-day years.

D7: =E6*$B$2/12

D7: =E6*(B7-B6)*$B$2/365

E6 is the initial loan amount; E7 is the remaining balance. A7 is the
payment number. B7 is the first payment date; B8 is the subsequent
payment date. C7 is the payment amount. D7 is the interest amount.

Caveat about B8: That formula will not work as needed if the day of
the month is 29 or later. (30 or later if the loan term does not
include a leap year, as yours does not). The formula can be changed
to handle those later days of the month. But it seems like an
unneeded complication in your case.

Caveat about D7 for 365-day years: Some lenders use 366 in leap
years. Again, the formula could be changed to handle that.

HTH.


----- original posting -----

 

[joeu2004]

Errata ....

E6:  =$B$1
[....]
D7:  =E6*(B7-B6)*$B$2/365

I forgot to mention:

D6: 12/15/2008

D6 is the loan origination date. The OP did not mention it. I assume
it is one month before the first payment date.