(17B/19B) GPM: initial payment

[Werner]

(05-27-2024 02:23 PM)Albert Chan Wrote:  We can remove loops, and get direct formula for pv/pmt
--> pv/pmt = -q^5 / c300 - (q^5-1)/(q-1) / c12

Hi Albert,
that is literally what I did in the very first reply to Sliderule's original post.
The only difference is that I tried to make use of the ('standard',high-precision) financial functions USPV,USFV etc and so I had to find the q% if q = 1 + q%/100 in your formula.
This is my latest concoction, no local variables:

Code:

GPM:PV+(USPV(I%÷12:12)×USFV((G%-USFV(I%÷12:12)×I%÷12)÷SPFV(I%÷12:12):N)+USPV(I%÷12:(M-N)×12)×SPFV(G%:N)÷SPFV(I%÷12:N×12))×PMT

or, with a bit of comments:

Code:

GPM:PV+
 (                                                      -- first N terms:
    USPV(I%÷12:12)                                      --   PV of 12 PMTs
   ×USFV((G%-USFV(I%÷12:12)×I%÷12)÷SPFV(I%÷12:12):N)    --   1 + K + K^2 + .. K^N-1, K = G/F, F = (1 + (I%/12)/100)^12
   +                                                    -- last M-N terms:
    USPV(I%÷12:(M-N)×12)                                --   PV of (M-N)x12 PMTs
   ×SPFV(G%:N)                                          --   multiply by G^N
   ÷SPFV(I%÷12:N×12)                                    --   bring to PV, divide by F^N = (1 + (I%12)/100)^(12xN)
 )×PMT

(I put PMT at the end so that it's the last entry in the solver menu)

Cheers, Werner