Formula FV and Date if not working

[Riggs609]

Hi, I figured I would make a new thread since I wrote too much confusing stuff in the other one, so hopefully this explain it better. I want to have a formula that will automatically yield an adjusted balance on the same day each month. Here is what I tried: =D6 - FV(D4/12, DATEDIF(D3, TODAY(), "m"), D2) where D6 is the loan amount, D4 is the interest rate, D3 is the starting date of the loan (1/15/2008), and D2 is the monthly payment amount. It adds the loan payment instead of deducting it and it does not add the interest rate in. so I changed to this
=D6 + FV(D4/12, DATEDIF(D3, TODAY(), "m"), D2) It now properly subtract the monthly payment amount but still does not add the interest. I hope that makes more sense but I can't figure this out at all, please help, thank you! Riggs

 

[joeu2004]

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:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]$100,000.00
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]$99,799.83
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]$99,597.66
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]$99,393.47
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD="align: right"]177
[/TD]
[TD="align: right"]$3,529.68
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD="align: right"]178
[/TD]
[TD="align: right"]$2,364.81
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD="align: right"]179
[/TD]
[TD="align: right"]$1,188.29
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD="align: right"]180
[/TD]
[TD="align: right"]$0.00
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Riggs609]

Thanks so much JoeU2004! That was a very detailed and understandable explanation. I appreciate you taking the time to help me with that. Have a nice weekend! Riggs

 

[Dryver14]

Not my Question but I have to agree, great explanation