Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 360-day Year Based Payment & Amortization Calculations?

I need to create a loan calculator within Excel that will calculate and
generate amortization schedule based upon a 360-day year (30 days per 12
mos). I found a "How to Create a Loan Amortization Schedule" in product
knowledge base, but have been unable to find how to base these calculations
on a 360-day year (30 days per 12 mos). Ahy help is greatly appreciated.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: 360-day Year Based Payment & Amortization Calculations?

Creating a Loan Calculator in Excel

1. Create the following columns: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
  1. In the Payment Number column, enter the numbers 1 through the total number of payments you want to calculate.
  2. In the Payment Date column, enter the first payment date. Then, in the cell below it, enter the formula "=EDATE(A2,1)" and drag it down to fill in the rest of the payment dates.
  3. In the Beginning Balance column, enter the loan amount.
  4. In the Payment column, enter the formula "=PMT(rate/12,360,beginning balance)" where "rate" is the annual interest rate and "beginning balance" is the balance at the beginning of the payment period. This will calculate the payment amount based on a 360-day year.
  5. In the Interest column, enter the formula "=beginning balance*rate/360" where "rate" is the annual interest rate. This will calculate the interest for the payment period based on a 360-day year.
  6. In the Principal column, enter the formula "=payment-interest" to calculate the principal payment for the payment period.
  7. In the Ending Balance column, enter the formula "=beginning balance-principal" to calculate the balance at the end of the payment period.
  8. Drag the formulas down to fill in the rest of the rows.
  9. To make the amortization schedule easier to read, you can format the cells as currency and adjust the decimal places as needed.

That's it! You now have a loan calculator that will calculate and generate an amortization schedule based upon a 360-day year. Let me know if you have any questions or if there's anything else I can help you with.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 360-day Year Based Payment & Amortization Calculations?

Use the regular financial functions. The 360-day year (12x30) simply refers
to the fact that each month is assumed to be have same number of days. So as
long as you express your term in months, Excel's financial functions will
properly calculate for you.

Regards,
Fred.

"Kelley from NC" <Kelley from wrote in message
...
I need to create a loan calculator within Excel that will calculate and
generate amortization schedule based upon a 360-day year (30 days per 12
mos). I found a "How to Create a Loan Amortization Schedule" in product
knowledge base, but have been unable to find how to base these
calculations
on a 360-day year (30 days per 12 mos). Ahy help is greatly appreciated.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change the payment period of amortization schedule gcip Excel Worksheet Functions 2 March 14th 07 04:01 AM
I need an amortization schedule with the first 6mo no payment? kmagolden Excel Worksheet Functions 0 October 24th 06 11:51 PM
Is there a fluctuating payment loan amortization template in XL? sandyfeatherston Excel Worksheet Functions 0 April 18th 06 02:26 AM
Amortization template with delay in first payment Lisa W Excel Discussion (Misc queries) 0 January 27th 06 09:38 PM
Loan Amortization Template - Amount of Final Payment Glenn Excel Discussion (Misc queries) 0 December 11th 04 02:05 AM


All times are GMT +1. The time now is 11:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"