Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 14 2023 05:31 PM - edited Oct 14 2023 05:32 PM

well you want a rate such that a present value of -0.14 will result in a value of 9500 after 52 cycles/periods. I don't see how that is possible. Please review the definitions of the arguments to make sure you are using it right:

The RATE function syntax has the following arguments:

Nper Required. The total number of payment periods in an annuity.

Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

Pv Required. The present value — the total amount that a series of future payments is worth now.

Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 21 2023 08:36 AM - edited Oct 21 2023 09:22 AM

@mtarler wrote: ``I don't see how that is possible``

For your edification....

First, when pmt=0, the __ exact__ periodic rate can be calculated simply with the formula:

=abs(fv/pv)^(1/nper) - 1

I use ABS to avoid dealing with the signed cash flows, in general. In this case, we can write:

=(9500 / 0.14)^(1/52) - 1

That displays 23.8555007215111%.

As a double-check, note that =FV(23.8555007215111%, 52, 0, -0.14) displays 9500.00000000016.

The infinitesimal difference is due, in part, because I rounded to the 15-digit rate. When I use the exact calculated rate, FV displays 9500.00000000001. __ That__ infinitesimal difference is probably due to internal rounding and binary arithmetic.

Finally, we __ can use__ RATE to calculate an

There is no __ good__ way to determine a "good" guess, especially since Excel algorithms are peculiar wrt "guesses". But with

=RATE(52, 0, -0.14, 9500, 0, 24%)

That displays 23.855500721511%. And using that exact result, FV displays 9500.00000000001.

But I caution that, in general, RATE uses an interative algorithm to derive its result, even though an exact result could be calculated when pmt=0. So sometimes, the RATE result has significant "error" (mathematically speaking).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 21 2023 01:54 PM

Joe User, thank you for jumping in as accounting is not my forte. I still don't understand how +/- don't matter. To me that solution results in -9500 not +9500

=-0.14*1.23855500721511^52

=-9500

Guess that is the engineer in me

=-0.14*1.23855500721511^52

=-9500

Guess that is the engineer in me

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 22 2023 04:49 PM - edited Oct 22 2023 10:19 PM

@mtarler wrote: ``I still don't understand how +/- don't matter``

It is not an accounting vs engineering thing. My father the CPA did not understand it. He turned to me the mathematician/scientist/engineer to explain it.

(Aside.... The OP did not say what the numbers 0.14 and 9500 mean. But it is easier to speak of them as "cash flows", since that is what the terms "pv" and "fv" refer to.).

I think my first exposure to signed cash flows was with the HP12-C calculator, 40 years ago. It used an internal formula similar to the one that is documented in the Excel PV help page.

But OMG, it was removed recently by the MSFT idiots who have been "simplifying" help pages to the point of being unhelpful. Fortunately, I captured (and corrected) an image from the Excel 2010 help page, to wit:

With pmt=0, algebraically the solution for FV becomes:

fv = -pv*(1+rate)^nper

And with pv=-0.14, nper=52 and rate=23.8555007215111% (rounded), we calculate fv=9500 (approximately), not -9500.

But I think the concept of signed cash flows is straight-forward, to wit: inflows and outflows should have opposite signs.

The choice of signs does not matter (it depends on point of view), as long as they are used consistently for each Excel function.

So as a personal preference, I usually choose signs so that the Excel function result should be positive.

(Caveat.... Not all Excel financial functions permit that flexibility. Historically, the functions that do not were developed by third parties and contributed to the ATP before they were mainstreamed in Excel 2007. For example, CUMPRINC allows only positive "pv".)