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