Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 07 Mar 2012 01:26:16 +0100Is it normal for Calc goal seek to take very long?https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/I made a spreadsheet for a 30 year loan, 360 months, with a constant payment, and asked goal seek to calculate the interest rate to make it pay off at .6% of original principal a month, calculating interest by the dollars*days for a 365.25 day year. The starting value of 6% per year was almost correct. The goal seek did not have an answer even after several minutes. Does Goal Seek make too many wild guesses?Sat, 18 Feb 2012 18:40:09 +0100https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/Answer by m.a.riosv for <p>I made a spreadsheet for a 30 year loan, 360 months, with a constant payment, and asked goal seek to calculate the interest rate to make it pay off at .6% of original principal a month, calculating interest by the dollars*days for a 365.25 day year. The starting value of 6% per year was almost correct. The goal seek did not have an answer even after several minutes. Does Goal Seek make too many wild guesses?</p>
https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?answer=74#post-id-74I think there is a function in calc to calculate the fixed payment with a know rate:
=PMT(0,06/12;30*12;100000;1)
In the LibreOffice help:
*PMT
Returns the periodic payment for an annuity with constant interest rates.
Syntax
PMT(Rate; NPer; PV; FV; Type)
Rate is the periodic interest rate.
NPer is the number of periods in which annuity is paid.
PV is the present value (cash value) in a sequence of payments.
FV (optional) is the desired value (future value) to be reached at the end of the periodic payments.
Type (optional) is the due date for the periodic payments. Type=1 is payment at the beginning and Type=0 is payment at the end of each period.
In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Example
What are the periodic payments at a yearly interest rate of 1.99% if the payment time is 3 years and the cash value is 25,000 currency units. There are 36 months as 36 payment periods, and the interest rate per payment period is 1.99%/12.
=PMT(1.99%/12;36;25000) = -715.96 currency units. The periodic monthly payment is therefore 715.96 currency units.*Sat, 18 Feb 2012 20:49:53 +0100https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?answer=74#post-id-74Answer by cloph for <p>I made a spreadsheet for a 30 year loan, 360 months, with a constant payment, and asked goal seek to calculate the interest rate to make it pay off at .6% of original principal a month, calculating interest by the dollars*days for a 365.25 day year. The starting value of 6% per year was almost correct. The goal seek did not have an answer even after several minutes. Does Goal Seek make too many wild guesses?</p>
https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?answer=210#post-id-210I'm not familiar with the English terms here, but this task seems simple enough for the goal seek to handle in a couple of seconds, not minutes.
So it's best to file a bug and attach the sample document in order to be able to debug it. https://www.libreoffice.org/get-help/bug
You might also check whether the solver does a better job in solving this specific problem.Mon, 20 Feb 2012 13:11:00 +0100https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?answer=210#post-id-210Comment by MagicFab for <p>I'm not familiar with the English terms here, but this task seems simple enough for the goal seek to handle in a couple of seconds, not minutes.</p>
<p>So it's best to file a bug and attach the sample document in order to be able to debug it. <a href="https://www.libreoffice.org/get-help/bug">https://www.libreoffice.org/get-help/bug</a></p>
<p>You might also check whether the solver does a better job in solving this specific problem.</p>
https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?comment=975#post-id-975If you end up filing a bug please do include a file showing an example of what you're trying to do.Wed, 07 Mar 2012 01:26:16 +0100https://ask.libreoffice.org/en/question/53/is-it-normal-for-calc-goal-seek-to-take-very-long/?comment=975#post-id-975