Free Microsoft Excel 2013 Quick Reference

Overdraft/Finance charge calculation

If I have a number of cells in a row:
A B C D (weeks)
55k 100k 125 135 i.e cumulative

I need to work out how much interest payments I have to make per week due to borrowing the 55k, 100k etc. What formula do i use in Excel to calculate this finance charge?


Is there some function in Excel that will calculate the finance charge on a
credit card? I tried to use the IPMT but it didn't quite work. Here's my
situation, so maybe if there's not a function there's a formula:

I don't use my credit card for anything new, so the only things that post to
my account is the payment and the finance charge. I had set up 2 different
formulas so I could calculate the finance charge for the beginning balance
and the new balance after the payment was made. I kept coming up a little
short, and then I read in my terms that it adds the finance charge to the
beginning balance daily, so my calculations are a little off.

Since my payment date isn't the same every month (annoying) I would have to
change the formula every month to match the number of days before and after
the payment is made. If there is a function where I can just type in the
number of days and it will calculate it that would be ideal.

Does anyone know anything about this?

Thanks

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

I've been trying to figure out how to calculate finance charges on auto loans for over a month, and I still have yet to figure out how to make a formula work. Has anyone ever done this before? Auto loans calculate interest on a daily basis, so days to first payment come into account. I cannot seem to get a functioning formula. Any help would be really appreciated, or even a place to start. Here is some data I have to work with to build a formula:

contract date: 05/03/11
first pmt date: 06/17/11
Term: 60 months
Payment: 472.01
Interest Rate: 17.99
Amount Financed: 18453.57

And finally, I know that the finance charge for this loan is $9867.03. What I'm trying to do is calculate participation, which means I need to calculate what the finance charge would be at 15.99%, and so forth. All the information is there, I just can't seem to figure it out and I'm pretty frustrated at it. Now, I know that the actual interest rate is closer to 18.02% from my company loan calculator, but I can't get it perfect.

I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula:
VLOOKUP(D32,Shipping!A2:B52,2).

My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.

I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these.

I’ve attached a sample spreadsheet, Shipping_Charge.xls.

In advance, I thank you for your help.

A-Amt Due
B-60 days past
C-90 days past
D- Finance Charge
E- Payment Amount

After entering the payment I would like it to subtract E from D first, if E is greater than D then the remainder of the payment is subtracted from C and if there is still payment available then subtracted from B. Meanwhile A will reflect the amount due after the payment. I've spent too many hours on trying to make this work. Someone PLEASE HELP!!!! Thanks

I need a formula that I could use to figure out finance charge on a monthty
basis on a credit card.

How do i create a formula in Excel to calculate the average percentage rate
of a 30 year financing period
For example Loan amount 135,000 montly payment amount 684.03 number of
payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
first 74 payments the monthly payment amount will be 742.53 and for the
remaining payments of 286 the monthly note will be 684.03

My company is looking to launch a new product. It will be a "value" version of an existing product, and to create it we will make about 20 changes to the existing product. Some of these will be increased charges for optional features. Others will be removing features to cut costs. The intention is to use these to finance reducing our mark-up on the core product, thereby reducing the price and driving volume.

I need to model this. I have a pricing model for the existing product, and my initial plan was to make the 20 changes, then goal-seek the increased profit back to normal levels by reducing mark-up, then apply a price-sensitivity formula to calculate the volumes to run through the model.

However, I need to able to value the contribution of each of the 20 changes. I normally do this by creating 20 scenarios (each one adds in an additional option) which are controlled from one cell - I then run a data table on this cell to get the value of each scenario. But I can't combine this with calculating my reduced mark-up through goal-seek.

I've also considered trying to calculate the commission reduction directly for each of the 20 changes - and then inputting this into the pricing model. But the calculations are quite complicated, and some of them involve the mark-up (eg finance charges and debt are calculated on total selling price). So I think I'd end up with a fag packet calc that then didn't give the answer expected when put into the proper model.

I'm wondering whether allowing circular references would help? But I'm not sure how the data table would cope with them?

If you've read this far - thank you! I'd really appreciate any suggestions.

Financial lease calculations.
There is an asset of fair value @ 3,512,000. There are 4 payments – 150,000 quarterly and yet the last one (after year) is purchase of this asset @ 3,200,000. Quaterly payments are made in advance. To record the value I need to use min of either fair value or NPV of MLP.
First of all, I need to calculate IRR, then MLP based on IRR. A colleague of mine told me that because of advance payments IRR should be calculated as IRR(3,362,000…150,000…150,000…150,000…3,200,000) saying that initial fair value should be decreased by 150,000. But as I understand it should be like IRR(3,512,000…150,000…150,000…150,000…150,000…3,200,000)

Appreciate if you can advise on these functions and correct lease treatment since I need to calculate impact on P&L & BS of finance charge and reduction in obligations. Unfortunetely never met with financial lease. If you know any useful links on this issue …I’ll be obliged to you for them.

Thanks,
Littleps

Does anyone have a formula to calculate an APR for an adjustable rate mortgage?
for example if I have a 200,000 - 30 year mortgage that has a fixed rate of
6% for 3 years and then adjusts to a rate of 7.75 every year after that
after that, with 1500 in finance charges.

So far I can figure out the APR without adjustment of
=rate(360,PMT(6%/12,360,200000),200000-1500)*12

Any suggestions to complete the formula?

Hi! i've attached a file for you guys reference.

what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10). but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10

can anyone please help to guide me or help me?

Hello All

I need a template that amortizes loans figuring interest the same way credit
card companies do. For example, credit card companies use the "Average Daily
Balance" ((APR/12/360 )*Average Daily Balance) method to determine your
finance charges each month. I noticed that there are several calculators
that use simple interest but I am always off by a several dollars when I try
to figure how much I need to pay each month to pay off the balances.

Thanks For Your Help
Geno1

Total Premium $65156.40, downpayment $16289.10. Amount financed is $48867.30
and the finance charge is $1633.86. The payment amount is $5611.24 and the
term is 9 months. The total of all payments is $50501.16. Does anyone know
how the cost of credit figured as a yearly rate is determined for a scenario
like this.

I don't know if this has already been discussed or not but I couldn't find
anything about it so here's my question:

I am working on a spreadsheet that calculates interest owed based on how
many months have been paid on a contract. The original spreadsheet was based
on a formula that took the date of the last payment and subtracted it from
the current date, then calculated the interest on that period of time. Well,
that doesn't work if the contract is a 2 year contract from 2001 and is
already over. We can't charge more interest than what they were originally
quoted, and we can't charge negative interest either. So I reworked the
formula to use a beginning date and calculate the ending date based on the
number of months in the contract and if the ending date has already lapsed,
to use that date. Otherwise it uses the current date.
I tell you all this to help you understand my problem. This sheet has an
amortization table built in. I want to use that to calculate the interest
already paid based on the months that have been paid on the contract. I can
vlookup the interest for the last payment, but how do I get the sum of that,
plus all previous months, and subtract that from the total finance charge? I
could just do it the way it was done before, but the interest charges were
always anywhere from a few cents to five dollars off. I want this to be dead
on accurate from now on, I don't like guesswork and approximations.

I have tried to write a formula to do this, but to no avail, so if anyone can help, please do. See the info below.

.............................24............30............36
3000 14999..........9.00%.......11.00%.....12.00%
15000 99999........9.50%.......11.50%.....12.50%
100000 249999.....9.00%......11.00%.....13.00%

I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%).

The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.

Can anyone help?

Thanks

Brett

How do I calculate the monthly payments using the "Add-On" method?
Thanks!

Hi!

Can someone please help me with this question regarding calculating the APR
that you see in the Federal Truth-In-Lending Disclosures when you sign a
contract for a car loan?

If I have these values, what is the fast way to get to the APR
1) Add on Rate (12%)
2) Term (36 months)
3) Loan start date and loan end date (Feb 1 2005 to Aug 5 2007)
4) Amount financed ($5790)
5) Finance charge ($746)

If I'm missing any information you need to help me with this please tell me.

I'm assuming it's the RATE function but I can't get it to work correctly.
Please help because my job depends on it :-)

A thousand thanks in advance!

I have exported a report out of Quickbooks with all my customers that have done business with us from 2005-2008. I want to be able to delete the customers from the worksheet that have done any business in 2008. So that I am left with a list of customers that have not done any business with us in 2008. Can a formula or macro do this for me. I don't just want to delete the rows that contain any dates in 2008, but delete the customer as a whole from the workbook.

TypeDateNumMemoNameItem@Home RealtyInvoice10/20/200522813BIC Clic Stic Black Barrel Red Trim White and Red Imprint Black Ink Medium Point@Home RealtyCSInvoice10/20/200522813Printing an additional color on the merchandise@Home RealtyADDCOLORInvoice10/20/200522813Shipping & Handling@Home RealtyShippingInvoice12/01/2005FC 2058Finance Charges on Overdue Balance@Home RealtyFin ChgInvoice07/18/200730753Silkscreen add web address to shirts@Home RealtySilkscreenInvoice07/18/200730753Silkscreening Set-up fee@Home RealtySet-upTotal @Home Realty1 Source ManufacturingInvoice08/16/2007311575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice08/16/200731157Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice08/16/200731157UPS Ground Shipping & Handling1 Source ManufacturingShippingInvoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/200731875Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice10/25/200731875Shipping & Handling1 Source ManufacturingShippingInvoice07/08/2008343575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice07/08/200834357Shipping & Handling1 Source ManufacturingShippingTotal 1 Source Manufacturing

Column A Column B Column C
Scenario A $ Depart Code
Debit Books 40 1010521
Credit Bank 40
Debit Computer 500 1010741
Credit Bank 500

Scenario B
Debit Books 40 1010521
Debit Computer 500 1010741
Credit Bank 540

Problem A
Could anyone suggest a macro that combine both expenses
(Computer & Books) into one amount with only one credit line
entry as shown in Scenario B(only for depart code 1010521 &
1010741).
Question:what's the source code?

Problem B
Can I create a macro that verify whether user entered a valid
depart code by matching the code entered in Column C against
a database table that contained a list of valid depart code.If code
entered is not in the database,a pop-up message box will prompt
the user to re-enter a valid depart code in Column C or in a
textbox form repeatedly until a valid depart code is accepted.
Let's us assume that depart code 1010521 is an invalid code.
Question:Any source code for this?

Problem C
A macro program that can automatically compute finance charges
figure if amount entered in Column B is within $5 to $40 based
on 1.5% rate,and 3% if amount is more than $450 but less than
$600.Let's assume finance charge in Scenario B need to be computed
The computed figure (1.5% X $40) & (3% X $500) has to be
automatically Debited immediatelly below or above the Credit line
related to Bank.
Question: Any source code for this?

[ This Message was edited by: fdtoo on 2002-12-09 05:46 ]

My form calculates a payment after adding all of the fields that affect the
total financed amount. Two of the fields that are added to the total financed
amount calculates the credit life and disability premium respectively. If the
credit life and/or the disability Insurance is not selected they return a
blank field.
As long as both Life and disability are both selected the payment
calculates fine. But if either one is not selected and therefore "blank"
then
the payment = "blank".
See the formula below.
How can I correct this problem? Thanks.

=IF(ISERROR(PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0)), "",-PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0))

CE26 and CE29 contain the formulas that are causing the problem.
Thanks.

My form calculates a payment after adding all of the fields that affect the
total financed amount. Two of the fields that are added to the total financed
amount calculates the credit life and disability premium respectively. If the
credit life and/or the disability Insurance is not selected they return a
blank field.

As long as both Life and disability are both selected the payment
calculates fine. But if either one is not selected and therefore "blank" then
the payment = "blank".

How can I correct this problem? Thanks.

Is it possibe to figure out monthly credit card payments since the finance
charges differ from month to month and the amount paid monthly may differ.
Lets say I have credit card with 7.99% APR a balance of $9500.00 and a
minimum monthly payment of $200.00. I don't always pay the minimum, but never
less, I would like to be able to start with the balance in one cell enter my
payment in another and have it subtract the payment from the balance then add
the monthly finance charge to come up with a new monthly minimum. I looked at
PMT & PV but they don't appear to what I need.

Thanks In Advance for you help
Joe

I need to create a formula for the following scenario. I need to do a freight
charges calculation. I have the number of lb, the rate per lb, a minimum
charge of $20. I am looking for a way to create a formula that automatically
calculates the correct freight amount when I enter the weight. The rate is
$0.20 per lb. So the formula should either enter the freight amount based on
the per lb rate, or $20 minimum of the per lb rate would total to less than
$20. So here is the example:
Cell A1= Weight in lbs
Cell B1 = Total Freight charges
The formula should be: B1 = Sum of (A1*0.20) if that total is >20. If the
total is

I need to create a formula for the following scenario. I need to do a freight
charges calculation. I have the number of lb, the rate per lb, a minimum
charge of $20. I am looking for a way to create a formula that automatically
calculates the correct freight amount when I enter the weight. The rate is
$0.20 per lb. So the formula should either enter the freight amount based on
the per lb rate, or $20 minimum of the per lb rate would total to less than
$20. So here is the example:
Cell A1= Weight in lbs
Cell B1 = Total Freight charges
The formula should be: B1 = Sum of (A1*0.20) if that total is >20. If the
total is <20, B1 should be 20.

I've been trying to figure it out, but can't seem to come up with a
solution. Any answers out there?

Thanks.