# How to calculate XIRR using Microsoft Excel to know how much your loan costs

To be honest I don’t suggest buying a car through a loan for many reasons, so I am going today to explain just one reason, & I think it will be enough for you to think again before buying a car using facilities.

At least when you decide to do that, you should know what you actually pay.

**Example**

If you want to buy a $10000 car, and you don’t have cash, you will start to look for a financial facility.

Finally, you find the chance for a loan charging you just 8%
yearly. **The employee will tell you the following:**

**1- **
We just charge **8%** yearly,
so $10000*8% = $800 suppose you will pay in full within two years so $800*2=
$1600.

**2-**
As is a loan you just have
to pay **$500** as other fees as it is not cash sales, & he will start give you
a list of reasons.

**3-**
So you have to pay just $1600+$500=
$2100 more during two years.

**4-**
First payment should just 10%
of the cash cost $10000*10%= **$1000**

**5-**
Monthly installment will be
just **$442.**

**6-**
Initial payment will be (10%
the cost + other fees+ one installment) =($1000+$500+$442) = **$1942**. (*Just 19.42%
of the cash cost*).

**7-**
Look how much you are
lucky, now you have a car loan costs you just 8%.

**Q: Are you happy?**

**A**: As accountant, I am ** not **happy at all, because easily
I will take his offer and go to my office, I will open Microsoft office Excel
& use a very simple function (

**XIRR**) to find that this loan will cost me

**27.24%**a year.

## Why the rate has been changed from 8% to 27.24%?!!

**1- **Because when you received the
car you directly paid 10% of cash value of the car, so the loan amount is **$9000**
and not $10000.

**2-** You paid **$500** more as other
fees.

**3-** You started to pay first
payment on the __same__ day you received the car.

**4-** You are paying __monthly installment,__
so the loan amount is reducing month by month (You are __not__ taking the car &
pay the loan and its interest __after 2 years in bulk__).

## Microsoft Excel Books

## Excel Sheet

## How do I use XIRR formula in MS Excel ?

Suppose you are the bank or the service provider so you **missed**
$10000 cash when you delivered the car, let us say on 1-1-2011, to your
customer so this amount is **negative**.

But the amount you ** received** starting from
1-1-2011 all are

**.**

*positive***A- **
In first column A enter:

**1.**
In cell A1 enter the word (Amount).

**2.**
In Cell A2 enter **-**10000
(**minus** ten thousand) the value you missed on 1-1-2011.

**3.**
In Cell A3 enter 442 the value you received on 1-1-2011.

**4.**
So on for all installment
till A26 which contain the last amount you should pay.

**B-**
In Second column B enter
the appropriate date beside each amount till B26 which has the date of 24^{th}
installment.

**C-**
In cell A27 enter this “=SUM(A3:A26)”
so you get the total of the amounts they have received from you.

**D-**
In cell A28 enter “=XIRR(A2:A26,B2:B26)”
(this is the Syntax = **XIRR**(**values**,**dates**)
so you get **27.24%** as a result.

## Excel will use this formula

You should do same when you get an offer, anyway I will be ready to
help calculate the rate for you, if you give all the needed data *(cash cost, Initial
payment amount, the dates, the monthly installment).*

**Always calculate before you buy.**

**Note:***this can work for any kind of loans having installments.*

Hope it helps.

## More by this Author

- 22
It is a good chance to read others' culture by simply reading some of their thinker' quotes.

- 0
Many people make mistakes when Calculating Weighted Average Cost of Inventory, so here we try to make the issue simpler using Microsoft Office Excel, I hope it will help you.

- 3
Here, I describe how to calculate standard deviation manually & by using Microsoft Office Excel. More, I speak about the benefits of using this formula in business life & how to use it.

## 4 comments

A car with loan will cost you more. But still people go for it because the need of the hour is a car.

Oooohhhh, I like this hub, Tb80. I bookmarked it for future reference. Nice explanation-very useful. Thanks.