Forum Home
Press F1
 
Thread ID: 55925 2005-03-23 02:16:00 XL Amortization B.M. (505) Press F1
Post ID Timestamp Content User
336980 2005-03-23 02:16:00 I’ve been trying to design a Amortization Table with XL, so far without success.

I’ve "googled" the Internet and found any number of programmes that can be purchased from $30 - $300 but no instructions on building one with XL.

However, I did find a free download on the Microsoft site designed for XL and it works fine, except, there is no way to change the frequency of payments. Weekly or Fortnightly is not available, only Monthly. Other than that it’s just what I want.

The reason I want to be able to change the frequency of payments is to compare the considerable savings to be had by paying more frequently.

Now, as a betting man I would wager that this is possible with XL, question is how.

I thought I might be able to “borrow” some of the formulas from the downloaded spreadsheet, but alas they are hidden, and or protected. :(

Any of you XL guns help with this one?

Cheers

Bob 		B.M. (505)
336981 2005-03-23 03:05:00 MS Works has a range of templates, one of which is a loan analysis that can be saved in Excel format - maybe that might suit. PM me with your email address and I'll send you an XL saved version of that template.

D 		d.murray (276)
336982 2005-03-23 05:37:00 Hi Bob, see the formula PMT. Say you have a loan for $10,000 over 5 years @ 10%pa and you want to know how much monthly or fortnightly payments are.

Monthly Payments
=PMT(10%/12,5*12,10000) returns $212.47/month

Fortnightly Payments
=PMT(10%/26,26*5,10000) returns $97.89/fortnight

Note that the first argument is the loan percentage so you need to work out what the rate is based upon dividing the annual percentage by the number of payments in 1 year (26 for a fortnight or 12 for monthly or 52 for weekly). The second argument is the number of payments so thats 26 payments in a year for 5 years is 26*5 = 130.

You can compare these results against an online loan calculator such as the following link and you will see the results are the same. www.nationalbank.co.nz

hth 		Parry (5696)
336983 2005-03-23 07:31:00 Jeeeez Parry, normally I would be most grateful for your advice, but this “PMT” scares the hell out of me. Personally I have no desire to =PMT!
My experience has been that PMT can confuse even the finest Amortization Table. :)

Are you sure there is no other way? :(

Cheers

Bob 		B.M. (505)
336984 2005-03-23 07:48:00 LOL. You may find it has sudden bouts of hostility for no reason but will right itself in a couple of days. Your job is to be understanding. I think were treading on thin ice here and I can feel the ice cracking.... :-) Parry (5696)
336985 2005-03-23 18:27:00 Thanks for that table David, received OK .

May do the job but comes up with a number of #values where it refers to apparently empty cells . Don’t know what the go is here but at least I can get at the formulas so may be able to adjust . Looks like a row may have been added or removed and thrown everything out one .

I note also that Parry’s formula is included in the calculations which may explain the #values . I’ll try again in a couple of days . :blush: 		B.M. (505)
1