Calc - Amortization

users
#1

I use Writer daily, but practically never use Calc, so I am pretty
helpless. Now I have a problem that calls for a spreadsheet;
actually, a script or plug-in or something to Calc.

The problem is that I need to calculate an amortization chart for a
loan of regular payments with an odd number of months, where the
interest is calculated monthly rather than on a daily basis. When the
loan was first made the term was supposed to be 300 months, but the
amount of the payment was miscalculated, so the borrower has been paying
slightly more. Using an HP-12C (yes, I still have one, and it still
works!) I find that it will amortize in slightly less than 299 months.
In order to find the true balance at any given time a custom chart must
be created.

I tried online services, but they cannot handle a mess like this. I
also tried Google Docs, but I can't find even basic help information,
like how to write <balance> x 045 / 12, or how to make the expression
do the calculation.

I could do this manually in Calc, one row at a time (ugh!), but I was
hoping that there existed a script or something that would set it up
for me and do all the calculations. I tried the LO extensions web page,
but couldn't find anything.

Any ideas or suggestions?

#2

I actually made a template for this back in the day, it automates the process, might work for you:

http://templates.openoffice.org/en/template/loanannuity-calculator-amortization

All the best,
Joel

#3

Hi :slight_smile:
Going with the line-by-line approach but with a fast twist.

Each line has identical calculations with the result feeding into the next line and then having the same calculation repeated doesn't it?  So it's something like
C1 = A1 * B1

A2 = C1
B2 = B1
C2 = A2 * B2

A3 = C2
B3 = B1
C3 = A3 * B3

I suspect a tad more complexity and there is probably a - in there somewhere (such as A2 = A1 - C1), but again more complex but all kinda boiling down to something like that?

So write out the first row and the 2nd one but in row 2 write

A2 = C1
B2 = $B$1
C2 = A2 * B2

Now select all 3 cells in row 2 (or do them individually if there are extra columns in between).  See the tiny black square down in the bottom-right corner of the 3 selected cells?  When your mouse hoovers over that it changes shape.  When it's the new shape drag all the way down the page until somewhere past the last row you need (around 300 ish).  Then just delete the rows you don't need and maybe make the last row bold.

Regards from
Tom :slight_smile:

#4

On Tue, 8 Oct 2013 18:05:54 +0100 (BST)
Tom Davies <tomdavies04@yahoo.co.uk> dijo:

Going with the line-by-line approach but with a fast twist.

Thanks, that worked great - all except for the dates column. But I can
just enter the date manually for the first of each year and leave the
rest of the months blank - it's easy enough to count down from January
if I need a month in the middle.

#5

Hi :slight_smile:
You are welcome :slight_smile:  I wanted to try the template idea but i've never been much good with them and managed to stuff-up this one too.  I tend to find it easier to build it from scratch for myself because then when it goes wrong i sometimes have a vague idea what i did wrong and it's easier to quickly scroll through the cells checking that one doesn't suddenly leap out as being blatantly wrong.

For dates you could try typing in 
January
February
and maybe do March too, just to give Calc an even bigger hint, and then select all the cells you just filled in with the date and use the little black square again to drag down and it 'should' pick-up the pattern and fill in the rest of the months.

I put "should" in sarcastic quotes because in life in general i find there is often a huge disconnect between what "should be" and "what is" (in BattleStar Galactica they say "the truth on the ground" (ironically)).  Sometimes programs guess at the continuation really easily but that would usually be when you really wanted the 2 months to be cycled endlessly making it impossible to get jan, feb, jan, feb ... and then it learns that just in time for you to really need it to list all the months.  Still, it's worth a try because it really 'should' work the way you need and it's easy enough to try extending it for a few cells as a test-run.  Other good tools for this are copy&paste from the mouse's right-click menu or using the keyboard short-cuts 
Ctrl x = cut  (x looks a bit like scissors?)
Ctrl c = copy  (c stands for copy)
Ctrl v = paste (err, happens to be nearby)
or a combination of the different copy&paste methods.

Regards from 
Tom :slight_smile: