Amortisation Table

Discuss the spreadsheet application

Amortisation Table

Postby Hylander » Thu Nov 24, 2011 2:28 pm

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

Postby crusader » Thu Nov 24, 2011 3:45 pm

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.
crusader
Volunteer
 
Posts: 478
Joined: Sun Jan 20, 2008 5:06 am

Re: Amortisation Table

Postby MrProgrammer » Sat Nov 26, 2011 4:38 am

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).
User avatar
MrProgrammer
Volunteer
 
Posts: 3557
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Amortisation Table

Postby Hylander » Mon Nov 28, 2011 9:31 pm

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

Postby MrProgrammer » Mon Nov 28, 2011 10:10 pm

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).
User avatar
MrProgrammer
Volunteer
 
Posts: 3557
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 16 guests