## Amortisation Table

### Amortisation Table

I found an .xls amortisation table which I downloaded into excel. I tried to open it into Libre Office and although it opened the calculations in the boxes would not work.

Does Libre Office have an amortisation table function or can you tell me how to run the .xls file correctly?
OOo 2.3.X on Ms Windows XP
Hylander

Posts: 2
Joined: Thu Oct 09, 2008 6:18 pm

### Re: Amortisation Table

Hylander wrote:Does Libre Office have an amortisation table function or can you tell me how to run the .xls file correctly?

http://www.vertex42.com/ExcelTemplates/ ... edule.html

The right lower side of the page has an OOo version.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
Volunteer

Posts: 478
Joined: Sun Jan 20, 2008 5:06 am

### Re: Amortisation Table

Hi, and welcome to the forum.

Hylander wrote:Does Libre Office have an amortisation table function?
If you mean that you want to calculate loan payments based on the loan amount (cell A1), loan term in years (B1), yearly interest rate (C1), and payment frequency per year (D1), in Calc the formula is =PMT(C1/D1;B1*D1;-A1). To borrow \$100,000 for 25 years at 10.75% we have A1=100000, B1=25, C1=.1075, so if D1=12 (monthly payments) the formula's value is \$962.09. Since the monthly payment is directly proportional to the loan amount, this answer means that payments for 25 years at 10.75% are \$962.09 per \$100,000 borrowed.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Volunteer

Posts: 3557
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Amortisation Table

Thanks MrProgrammer for your help. However, I need a table that shows me the changing variations each month of the principal and interest paid
OOo 2.3.X on Ms Windows XP
Hylander

Posts: 2
Joined: Thu Oct 09, 2008 6:18 pm

### Re: Amortisation Table

Enter 1 in cell A5 and drag the Fill Handle down as needed to create 1, 2, 3, …, up to the needed number of payments. As explained in Help, the principal payment will be =PPMT(C\$1/D\$1;A5;B\$1*D\$1;-A\$1) so put that formula in B5, and the interest payment will be =IPMT(C\$1/D\$1;A5;B\$1*D\$1;-A\$1) so put that formula in C5. Then select B5 and C5 and double click the fill handle to fill these formulas down to match column A. Note that each Bn+Cn sum (PPMT+IPMT) matches the value calculated by PMT.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Volunteer

Posts: 3557
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA