First, I suggest that you sign your cash flows so that financial function always returns a positive number.
In order for FV to return a positive number, the "pv" parameter must be negative. For a loan, the "pmt" parameter is positive, the opposite sign of the "pv" parameter.
-----
Second, the correct usage is simply:
=FV($D$4/12, nper, $D$2, -$D$6)
That returns the outstanding balance after "nper" payments -- "nper" months since we divide the annual interest rate (D4) by 12.
I use absolute references ($D$4) so that the formula can be copied down a column, in case that is your intent.
You will see that the formula implicitly takes the interest amt into account by subtracting two balances. For example:
=FV($D$4/12, 1, $D$2, -$D$6) - FV($D$4/12, 0, $D$2, -$D$6)
is the amount of principal paid by the payments between period 0 and period 1. And:
=$D$2*(1-0) - (FV($D$4/12, 1, $D$2, -$D$6) - FV($D$4/12, 0, $D$2, -$D$6))
is the amount of interest paid by the payments between period 0 and period 1.
You might note that FV($D$4/12, 0, $D$2, -$D$6) = D6. And $D$2*(1-0) = D2. But the formulas above work for any two period numbers. They do not even need to be adjacent periods.
-----
Finally, let's discuss the "nper" parameter. You state that you want to "automatically yield an adjusted balance on the
same day each month".
DATEDIF($D$3,TODAY(),"m") does not do that, unless TODAY() happens to be on the same day of the month as D3 is.
Also, there are issues when D3 is the last day of the month. Typically in that case, we want each payment to be the last day of
its month -- which might be 28, 29, 30 or 31.
If you intend to build a monthly amortization schedule, we can avoid all that by putting the payment
number into a column of the table.
For example, for a loan of $100,000 (D6) at 12% (D4) annual interest rate for 180 (D5) months and a monthly payment =PMT(D4/12, D5, -D6) in D2:
| C
| D
|
8
| 0
| $100,000.00
|
9
| 1
| $99,799.83
|
10
| 2
| $99,597.66
|
11
| 3
| $99,393.47
|
12
| 177
| $3,529.68
|
13
| 178
| $2,364.81
|
14
| 179
| $1,188.29
|
15
| 180
| $0.00
|
<tbody>
</tbody>
Code:
Formulas:
D8: =D6
D9: =FV($D$4/12, C9, $D$2, -$D$6)
Copy D9 into D10:D15
However, if you want the outstanding balance on the same day of today's month, you might consider:
=FV($D$4/12, DATEDIF($D$3, TODAY() - DAY(TODAY()) + DAY($D$3), "m"), $D$2, -$D$6)
That works for days of the month 28 or less. Adjustments are be needed to work for days of the month 29, 30 and 31.
-----
Finally, let's talk about the monthly "pmt" in D2.
Note that we did not explicitly round the result of the =PMT(...) above. That obviates the need to treat the last payment differently.
And I suggest that you use the unrounded payment for now.
If we do not round the payment, we might consider the following formula in D9 to calculate the outstanding balance:
=$D$6 + CUMPRINC($D$4/12, $D$5, $D$6, 1, C9, 0)
But I do not recommend CUMPRINC for several reasons. First, the "pv" parameter must be positive, and CUMPRINC returns a negative number. Second, the "pmt" amount is calculated internally; we cannot control it as desired below.
However, in the real world, the "pmt" parameter must be rounded at least to the cent. And sometimes, the "pmt" parameter is rounded to much less precision; for example, to the dollar.
That might result in fewer payments; and the last payment might be different.
These are complications that go far beyond your question. I just wanted to put them out there to make you aware.
