# Top Excel Formulas for IFRS

This is another useful tip I would like to give you. With these formulas, you can really speed up your IFRS accounting process, especially when dealing with sophisticated calculations. So let’s start.

## Top Financial Formula #1: IRR

### What is IRR?

IRR formula calculates Internal Rate of Return for series of cash flows. The cash flows must be expressed as numbers or values. And, they must occur in regular intervals—daily, weekly, monthly, annually, whatever you need.

**Have you already checked out the IFRS Kit ? It’s a full IFRS learning package with more than 40 hours of private video tutorials, more than 140 IFRS case studies solved in Excel, more than 180 pages of handouts and many bonuses included. If you take action today and subscribe to the IFRS Kit, you’ll get it at discount! Click here to check it out!**

*Special For You!*What is internal rate of return? Simply speaking, it is an interest that you pay on initial loan (which is a negative value) with regular installments in later periods (which are positive values). Installments do not need to be the same in size, though.

### Where do you need to apply IRR?

Certainly, if you need to perform valuation of financial assets or liabilities in line with IAS 39/IFRS 9, you need to calculate effective interest rate—that’s exactly internal rate of return for cash flows from your financial instrument.

Also, lease accounting in line with IAS 17 requires actuarial method to be applied. Sure you have to

use IRR there. If you wish to read more about internal rate of return, please refer to our article How to calculate interest rate implicit in the lease.

## Top Financial Formula #2: NPV

### What is NPV?

NPV calculates Net Present Value of an investment by using predetermined discount rate and series of cash flows: investment, future payments (negative values) and future receipts (positive values).

Net Present Value is a difference between the present value (or discounted) cash inflows and the present value (discounted) cash outflows of certain project. So, again put more simply, it is your return on investment expressed in today’s money.

### Where do you apply NPV?

Net Present Value is a great formula for computation of potential profit or loss from the project and thus is used very widely for project appraisals.

In IFRS, NPV is used broadly in value in use calculation according to IAS 36. As you might know, value in use is critical for determining asset’s impairment loss.

Also, NPV is sometimes used in calculation of amount of provision according to IAS 37. When you have some onerous long-term contract, or some long-term restructuring, then NPV of cash flows from that restructuring or contract can well serve as the amount of your provision.

## Top Financial Formula #3: YIELD

YIELD calculates on a security that pays periodic interest—for example, bonds or treasury bills. If an investor wants to know his real rate of return on some bond, then he usually looks for yield.

Again a little simplification: yield is internal rate of return of a series of cash flows from the security — initial purchasing price, periodic coupon or interest payments and redemption price (how much investor gets from the issuer of bond at its maturity).

### Where do you apply YIELD?

YIELD formula widely applies mainly for valuation of fixed term securities in line with IAS 39 / IFRS 9. There are so many securities not publicly traded on the stock exchange and their fair value must be estimated by extrapolation along yield curve.

Also, when you are trying to determine appropriate discount rate for valuation of liabilities from employee benefits in line with IAS 19, then it is quite handy to know how to apply YIELD formula and extrapolate along yield curve.

## Download Excel Examples

OK, the last formula was a bit difficult, but otherwise, if you learn to apply these 3, your IFRS accounting skills will certainly improve. For your convenience, I am enclosing Excel file with illustrative short examples of all 3 formulas above.

### Download Excel Examples!

JOIN OUR FREE NEWSLETTER AND GET

report "Top 7 IFRS Mistakes" + free IFRS mini-course

Please check your inbox to confirm your subscription.

## 15 Comments

### Leave a Reply Cancel reply

### Recent Comments

- Max on IAS 21 The Effects of Changes in Foreign Exchange Rates
- Paul on Example: Consolidation with Foreign Currencies
- Senpai on How to Make Cash Flow Projections for Impairment Testing under IAS 36
- Anjum on 015: How to prepare financial statements when going concern does not apply?
- Silvia on How to calculate bad debt provision under IFRS 9

### Categories

- About IFRS (14)
- Accounting estimates (IAS 8) (5)
- Accounting policies (3)
- Consolidation and Groups (20)
- Employees (8)
- FAQ (1)
- Financial Instruments (45)
- Financial Statements (23)
- Foreign currency (9)
- How To (18)
- IFRS Accounting (64)
- IFRS Summaries (28)
- IFRS videos (40)
- Impairment of assets (6)
- Income Tax (9)
- Intangible assets (8)
- Inventories (14)
- Leases (16)
- Most popular (6)
- Not just IFRS (9)
- Podcast (40)
- PPE (IAS 16 and related) (38)
- Provisions and Contingencies (5)
- Revenue recognition (19)
- Sectors&Industries (3)
- Uncategorized (2)
- US GAAP (2)

Thanx for excel, it really helps!

ur summary presentation on the ias 1, & 16 has been really helpful. could it be possible to have one on 8, and others.

Thanks

Yes, qwame, I am adding new summaries all the time, just subscribe and you’ll get info about new vids. It takes time so please be patient. And thank you for the comment!

Silvia, IFRSbox.com

You are a genius!! Thanks for the excel file and the explanations.

Thank you, Yas 🙂

Your article is brilliant, in fact all articles I’ve read are really concise and easy to understand. However I’m struggling in understanding the difference between the IRR and EIR. Sorry if this seems a silly question.

Ho Aaliyah, it’s not a silly question, because in fact, there’s not much difference between these 2 rates.

IRR = internal rate of return = it’s the rate that if you apply it to your cash flows and discount them to present value, total net present value is zero.

EIR = effective interest rate = in fact, this is the internal rate of return of the cash flows from your loan, lease or whatever you have. Just the definition is set in a different way, because it says that EIR is the rate that exactly discounts estimated future cash payments or receipts through the expected life of the financial instrument to the net carrying amount of the financial asset or liability. In other words – it is the internal rate of return of all future cash payments and receipts plus net carrying amount in the present time.

Hope it helps.

Thank you, Silvia

Extremely useful info!

I had really hard time presenting the auditors the NPV’s and IRR’s of some debt portfolios

You made it VERY clear.

Thanks again

Thank you Silvia. Your IFRS teaching helping me to further strengthen my knowledge.

you are great 🙂

I have been using Microsoft Excel for donkey years, yet i don’t even know such great formula. 🙂

Thank you, Silvia.

Hi, Silvia

You are doing a great working by helping the students with your knowledge. I really appreciate your work on IFRS, I also liked your videos on youtube. Your explanation about IFRS is very good and easy to understand.

Hi Silvia, Regarding IRR calculation, if a bond purchase price includes accrued interest at the time of bond purchase as well as premium on face value, should we calculate effective rate based on actual purchase price (and including full coupon payment on first coupon date) or by excluding accrued interest from the price (and excluding accrued interest from total c0upon payment on first coupon date)?

Further, At initial recognition, is the bond recognized at purchase price including accrued interest or without including accrued interest?

Thanks a million.

Thanks Silvia for your worthy contribution.

Just one question as to why IAS-16 use the words depreciation and amortisation, interchangeably. A common understanding is that the word amortisation is used for intangible assets.