Looking for excel help

[Reptar]

Hi there,

I have created an installment calculator for my place of employment. I'm having a small issue getting part of it to function properly. If you have excel experience and would be intersted in assisting me real quick, please let me know!!

Thank you,

[Raphtheone]

What do you need?

[gamingmuscle]

What do you need?

What he said =P

[Reptar]

I have created a monthly installment calculator as I work for a bank. I deal with charged-off loans meaning the full balance is due and we work out payment arrangements. My original installatoin of the payment calulator used the PMT formula for calculating a monthly payment, and then had another section for using the NPER formula for a quick way to determine the term based on a specific payment amount.

The issue with this is that both of those formulas are based of the present value (PV). Originally we were inputting the full payoff. This can cause issues because most of these payoff include late fees which should not be included in the PV since they do not accrue interest. So basically now I'm trying to re-work the calculator and the results are not matching up.

That's the short version. If you think you can help, I can PM you or email you with a copy of the file for better reference.

Thank you,

[gamingmuscle]

I have created a monthly installment calculator as I work for a bank. I deal with charged-off loans meaning the full balance is due and we work out payment arrangements. My original installatoin of the payment calulator used the PMT formula for calculating a monthly payment, and then had another section for using the NPER formula for a quick way to determine the term based on a specific payment amount.

The issue with this is that both of those formulas are based of the present value (PV). Originally we were inputting the full payoff. This can cause issues because most of these payoff include late fees which should not be included in the PV since they do not accrue interest. So basically now I'm trying to re-work the calculator and the results are not matching up.

That's the short version. If you think you can help, I can PM you or email you with a copy of the file for better reference.

Thank you,

I have no idea what PMT/NPER formulas are...but if you you don't include fees in the PV can't you just calculate the non fee PV in a cell and pass/reference that in the formulas? Or do you need to include the fees?

EDIT oh they are excel formulas...give me a second to look at them.

Edit 2:
PMT(PV+fees)=PMT(rate,term,PV)+PMT(0,term,fees)?

NPER hmmmm

only thing I can think of is something like: NPER(0,pmt,(PV(rate,NPER(rate,pmt',pv),pmt')+fees))

[Reptar]

NPER is the formula to calulate the term. So for example if you use the PMT as you listed: PMT(PV+fees)=PMT(rate,term,PV)+PMT(0,term,fees). In this scneario you plug in all the criteria and lets say you put a term of 30 months and it gives you a monthly payment of 300.00.

In the other section, you should be able to input the 300.00 payment and the cell with the NPER formula should calculate the 30 month term. Point being, one calculates a payment amount while the other is the term. Ever since I've had to calculate the fees in this scenario without the interest, I'm having trouble getting things to match off.

If I use a term of 30 months in the PMT formula and I get a 300.00 monthly payment, then plug that 300.00 in the NPER formula, I'm getting either 29, or 31, or 32 depending on the amount of fees (the more fees, the larger the variance).

TLDR: these numbers need to match up but I'm struggling now that their are fees in there.

edit: =IF(I7=0," ",NPER(D13/12,-I7,D9)+(NPER(0,-I7,D10)))

That is my current formula for the term. I7 is the cell I'm using to enter any random payment amount. I'm using the above concept for the NPER formula.

[Kulanae]

It is probably a rounding error. You have division by 12, so you probably need to add a Round to 2 decimal places.

[Deleted]

Could you give us the numbers you're plugging in (D13,I7, D9 and D10 in this case), what the wanted result would be and what the actual result is? would help us debug the issue.

[gamingmuscle]

Ok so PV isn't what I thought it was.

NPER(0,pmt,Total Value)

Total value= PMT(rate,term,pv)*term+fees

- - - Updated - - -

Simple little test sheet
A | B
Value | 5000
Fees | 123
term | 30
rate | 0.08
Total |$13,447.12 =ABS(B6*B3) or =-CUMIPMT(B4,B3,B1,1,B3,0)+B1+B2
PMT= ($448.24) =PMT(B4,B3,B1)+PMT(0,B3,B2)
NPER= 30 =NPER(0,B6,B5)

[Aldred]

You could just put your spreadsheet up on Google Docs or something, then let people work on copies from there. It'd be solved in no time!

[Reptar]

Evidently my work has google docs blocked. Let me try another idea.





Picture one has D17 highlighted so you can see the payment formulae I have use. It gives me a payment amount of $617.15 I then plug this amount into I7 as L7 will generate the term with the NPER formula (picture 2). I used a term of 30 months, so the issue is L7 is returning 29 instead of 30. To me this sounds like a rounding error, but I'm having an issue finding the exact root of the issue.

[MetroStratics]

Excel is a wonderful program and super customizable. I wasn't aware you could make a calculator like this so I am learning as well. Sorry I couldn't be of help ;(

[gamingmuscle]

stuff

L7=NPER(0,D17,D9+D10+CURIPMT(D13/12,D12,D9,1,D12,0))

which based on your screen shot will give 30...which seems kind of silly to do since you have to know the term already =P

[Aldred]

Thanks for the screen shots. I think I understand better now. brb!

[Reptar]

L7=NPER(0,D17,D9+D10+CURIPMT(D13/12,D12,D9,1,D12,0))

which based on your screen shot will give 30...which seems kind of silly to do since you have to know the term already =P

Yeah, cells I7 is supposed to be a quick option. For example, we aren't not supposed to go out further than 30 months on an arrangement. To go further you need supervisor approval. So in this example 617.xx would be the minimal payment for 30 months with these figures. I7's function would be for if the customer is like, "Hey, I can pay 500/mo". This way we can take a term to our supervisor for quick reference. The NPER formula will also error out when the interest accrual outweights the monthly payment.

[Flame6]

Anyone get slightly worried to find out a bank uses an inaccurate spreadsheet to calculate payments? (just having fun ) If these guys can't help you than I recommend googling excel forums. In the past I've had good luck with the more specialized hard core forums.

[Reptar]

Anyone get slightly worried to find out a bank uses an inaccurate spreadsheet to calculate payments? (just having fun ) If these guys can't help you than I recommend googling excel forums. In the past I've had good luck with the more specialized hard core forums.

hahaha! It's not in use yet =)

[Aldred]

I'm stuck! I assume it's some kind of rounding problem since it gets worse the larger the term you use, but I can't see what and I'd hope it's not with the PMT or NPER functions themselves (haven't used them before - but I understand them now).

I'll take another look tomorrow.

[Reptar]

I'm in the same boat. My initial thought L7 would produce something like (13.68) + (3.68) and instead of producing 17.36, it would round to 14 + 4 = 18, but I still can't find out if this is it.

[gamingmuscle]

It's not a rounding error.

From my understanding NPER basic iteratively guesses the duration of the loan....you would basically need to do this with a serious of formulas.