Help!!! Using a What-if without using solver or Goal seek....

  • Thread starter Thread starter kittenkj
  • Start date Start date
K

kittenkj

I am trying to create a what-if spreadsheet for comparison purposes t
show if extra repayments were made how long it would take to pay of
and how much money would be saved. This is pretty easy using goal see
or solver but its an advanced user skill and doesnt help to pu
something more permanent in place on a spreadsheet to make it "idio
proof" for the average user. Years ago Excel used to have WHATIF as on
of their functions but itappears to have been replaced wiith the Goa
Seek option. Does anyone have any ideas? There has to be a way to d
this.

Cheers
Kitten, Sydney Australi
 
Have you tried the Scenarios option? That can give you a dropdown menu
on the users toolbar.

Judith
 
Scenarios does not help. Still requires advanced skills to do it.
want it on a spreadsheet that automates and is idiot proof so I nee
the code unfortunately. Thanks anyway.....Can anyone help with thi
one
 
Hi Kitten!

You can use the NPER function for this problem.

Lets assume a mortgage of 100000 for 10 years at an APR12 of 6.5%

Payments will be:

=PMT(6.5%/12,10*12,100000,0,0)
Returns: -1135.47977220027

Now let's assume that we make additional payments of 100 per month so
that the payments become -1135.48-100

=NPER(6.5%/12,-1135.48-100,100000,0,0)
Returns: 106.813517327543

The non integer part of the return indicates a need to make an above
normal 106th payment or a less than normal 107th payment.

If you use typical accelerated repayment schemes common in Australia
whereby 50% of the monthly payment is paid every two weeks then you
need to use:

=NPER((1+6.5%/12)^(12/26)-1,-1135.48/2,100000,0,0)
Returns: 232.341608621325

But this is the number of fortnights and it works out at just below 9
years. The difficulty in the above formula is that you have to
calculate the fortnightly effective equivalent of the APR12.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Kitten!

Always pleased to help and for confirmation that things work as
advertised; useful for Google searchers

--
Regards
Norman Harker MVP (Excel) (aka Captain Marvel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top