IRR,TWR, which to use and when..

Leveraging, renting vs owning, making an investment or buying a home?
Post Reply
Evruss57
Newcomer
Newcomer
Posts: 3
Joined: 31 Jul 2014 18:35

IRR,TWR, which to use and when..

Post by Evruss57 »

I lend money to real estate remodeling companies.
Below are inputs to a typical (actual) project.
I am trying to figure out how to calculate (in excel) my rate of return as the lender.

So I lent $221,000 (along with a construction draw allowance of $35,000) to finish the project.
The borrower has sufficient skin in the game and the project passed my due diligence.
I received monthly interest payments and a lump sum at closing (see below).

I can not seem to figure out a rate of return for the actual time period that makes sense...
Any help would be appreciated.

Evruss57

Date Deposits & Withdrawals
Jan 24/14 ($221,000.00) Origination
Feb 18/14 ($9,205.00) Construction Draw
Feb 24/14 $3,515.73 Interest received
Mar 3/14 ($3,375.44) Construction Draw
Mar 10/14 ($613.68) Construction Draw
Mar 17/14 ($12,297.40) Construction Draw
Mar 27/14 $3,515.73 Interest Received
Mar 31/14 ($6,489.20) Construction Draw
Apr 21/14 ($3,019.18) Construction Draw
Apr 24/14 $3,515.73 Interest received
May 30/14 $268,016.03 Final balance paid in full

What is my holding period return on a time weighted basis?
Is there a way to input in excel to simplify my work?
ig17
Veteran Contributor
Veteran Contributor
Posts: 3418
Joined: 21 Feb 2005 20:54

Re: IRR,TWR, which to use and when..

Post by ig17 »

Use XIRR function. Enter the dates and the cashflows exactly as you did above.

Note that XIRR calculates annualized rate of return. To calculate your RoR for the given period, change the last date to Dec 31.
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13271
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: IRR,TWR, which to use and when..

Post by Peculiar_Investor »

Evruss57 wrote:I lend money to real estate remodeling companies.
Given the plural, I'm guessing this a business?
I am trying to figure out how to calculate (in excel) my rate of return as the lender.

So I lent $221,000 (along with a construction draw allowance of $35,000) to finish the project.
The borrower has sufficient skin in the game and the project passed my due diligence.
I'm left to scratch my head how someone would undertake lending $221K to a project, which passed due diligence, without knowing how to calculate your rate of return. Wouldn't knowing the return on your investment be a critical part of that due diligence?
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Evruss57
Newcomer
Newcomer
Posts: 3
Joined: 31 Jul 2014 18:35

Re: IRR,TWR, which to use and when..

Post by Evruss57 »

Peculiar...
I have been lending money for eleven years (231 projects) and it is very profitable.
My extrapolated return using my HP12-c is 22.91% per year.
I am looking for an excel solution that takes less manual entry and is easily repeatable.
The XIRR function I have been using per my understanding does not work inter year.

My due diligence in question isn't the business model it is the viability of each given project against other alternatives.
I.E. location, floor plan, neighborhood amount of money the contractor is bringing to the table, experience etc.

However thanks for your input.
Evruss57
Evruss57
Newcomer
Newcomer
Posts: 3
Joined: 31 Jul 2014 18:35

Re: IRR,TWR, which to use and when..

Post by Evruss57 »

ig17

Thanks for your input. Will this work in a period over a year end? I.E. Sept 15th to say Feb 15th? Or would I just input each project fictitiously starting on Jan 1 and input numbers draws etc. 15 days later with interest paid in 30 day increments etc. and get rid of the "actual" dates?

Thanks Evruss57
twa2w
Veteran Contributor
Veteran Contributor
Posts: 2054
Joined: 22 Feb 2005 13:08

Re: IRR,TWR, which to use and when..

Post by twa2w »

A quick back of the envelope calculation puts your return on that project at better than 25% annualized. Of course your real rate of return on your money will be less given some of it will sit between projects or have to be held pending advances etc.

When you lend the money, don't you have the borrower sign a promisory note or a mortgage with an interest rate set out.

These are high risk and it only takes one or two bad deals to really wreak havoc with your returns.
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: IRR,TWR, which to use and when..

Post by AltaRed »

My initial impression is this rate of return might border on ursury rates in Canada but obviously not. Still, the risk may be better than being in the Visa/MC or PayDay loan business. Surely small companies have access to cheaper construction loans than that in this country.
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: IRR,TWR, which to use and when..

Post by pmj »

It's fascinating to see how several of us analyze off-topic aspects of unusual questions :oops:.

I was mystified by the very precise interest payment .... which happens to be a few cents off 1.59% of the initial loan value :?:
Peter

Patrick Hutber: Improvement means deterioration
mr_l
Contributor
Contributor
Posts: 164
Joined: 23 Dec 2013 00:06

Re: IRR,TWR, which to use and when..

Post by mr_l »

Will this work in a period over a year end? I.E. Sept 15th to say Feb 15th?
I would try making the end date Sept 14 instead of Feb 15, and see if that works.
Post Reply