Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 94591 times)

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2810
  • Total likes: 155
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1215 on: January 15, 2019, 04:36:10 PM »
I can use help figuring out how much money in mortgage loans we need to cover our overhead.
how many months and how many dollars needed to cover ourselves

when will the payments be able to match the loans we give out to be self sustained

i.e. if we give out 1m in loans @7%  we would only make x per year /12 is y per month
if our overhead is 20k a month - how many loans need to be out etc.

willing to pay - feel free to dm
What is the structure of these loans? Are you talking about a mortgage style loan? And if so, what is the term? Or maybe an indefinite loan term where they're paying monthly interest only payments?

ETA: Any type can be calculated pretty easily, in excel or otherwise. But it would be hard to give a specific answer without more info.
« Last Edit: January 15, 2019, 05:06:31 PM by skyguy918 »

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2592
  • Total likes: 29
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1216 on: January 15, 2019, 08:30:41 PM »
I can use help figuring out how much money in mortgage loans we need to cover our overhead.
how many months and how many dollars needed to cover ourselves

when will the payments be able to match the loans we give out to be self sustained

i.e. if we give out 1m in loans @7%  we would only make x per year /12 is y per month
if our overhead is 20k a month - how many loans need to be out etc.

willing to pay - feel free to dm
If I'm understanding you correctly, the math is overhead times 12 divided by the interest rate.
So 20k x12 is 240k, divided by 7% is 3,428,571 in loans needed.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7185
  • Total likes: 272
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1217 on: January 16, 2019, 09:29:17 PM »
I can use help figuring out how much money in mortgage loans we need to cover our overhead.
how many months and how many dollars needed to cover ourselves

when will the payments be able to match the loans we give out to be self sustained

i.e. if we give out 1m in loans @7%  we would only make x per year /12 is y per month
if our overhead is 20k a month - how many loans need to be out etc.

willing to pay - feel free to dm
Are you a bank?

You need a business plan..

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1218 on: January 20, 2019, 02:56:55 PM »
Are you a bank?

You need a business plan..

Can you help if I want to create a personal "bank" type - owner funded
you can only make a first impression ONCE

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1219 on: January 20, 2019, 02:57:58 PM »
What is the structure of these loans? Are you talking about a mortgage style loan? And if so, what is the term? Or maybe an indefinite loan term where they're paying monthly interest only payments?

ETA: Any type can be calculated pretty easily, in excel or otherwise. But it would be hard to give a specific answer without more info.

im looking at a 5 year loan, would love to discuss. pm?
you can only make a first impression ONCE

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1220 on: January 20, 2019, 02:59:15 PM »
If I'm understanding you correctly, the math is overhead times 12 divided by the interest rate.
So 20k x12 is 240k, divided by 7% is 3,428,571 in loans needed.
so if I start with 3.5 i'm good to go?
you can only make a first impression ONCE

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7185
  • Total likes: 272
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1221 on: January 20, 2019, 03:59:15 PM »
I can use help figuring out how much money in mortgage loans we need to cover our overhead.
how many months and how many dollars needed to cover ourselves

when will the payments be able to match the loans we give out to be self sustained

i.e. if we give out 1m in loans @7%  we would only make x per year /12 is y per month
if our overhead is 20k a month - how many loans need to be out etc.

willing to pay - feel free to dm
Start with this
https://www.calculator.net/loan-calculator.html

Just plug in you numbers and look at the amortization table.
To get 20k+ of interest income @7% you would need to have 4mm out all the time. Also need to make sure you are turning these over so all p returned is lent out again.

This assumes a zero default rate. - obviously wrong.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2592
  • Total likes: 29
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1222 on: January 20, 2019, 05:11:31 PM »
Start with this
https://www.calculator.net/loan-calculator.html

Just plug in you numbers and look at the amortization table.
To get 20k+ of interest income @7% you would need to have 4mm out all the time. Also need to make sure you are turning these over so all p returned is lent out again.

This assumes a zero default rate. - obviously wrong.
How do you get 4mm, the loans are probably interest only so the interest payment will be stable?

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7185
  • Total likes: 272
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1223 on: January 20, 2019, 05:14:17 PM »
How do you get 4mm, the loans are probably interest only so the interest payment will be stable?
.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2592
  • Total likes: 29
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1224 on: January 20, 2019, 05:16:54 PM »
so if I start with 3.5 i'm good to go?
You need to understand the math before you give people millions of dollars.
If the loan is "interest only" then 7% means that for every dollar you lend, the borrower will owe you 7 cents per year. So  if you lend out 3.5mm, the borrower will owe you a yearly amount of 3.5mm times 7% (3,500,000 x .07) which is 245k. If you split that between 12 months, you get 20,416 a month.

There are usually fees that borrowers charge with these types of loans that can generate additional income.
« Last Edit: January 20, 2019, 05:21:59 PM by yitzf »

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2592
  • Total likes: 29
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1225 on: January 20, 2019, 05:19:18 PM »
.
That's with principal payments reducing the balance. I would think that these types of loans are typically interest only.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7185
  • Total likes: 272
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1226 on: January 20, 2019, 05:19:56 PM »
That's with principal payments reducing the balance. I would think that these types of loans are typically interest only.
Oh.. no idea he called it a mortgage..

Offline Am

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2011
  • Posts: 947
  • Total likes: 1
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1227 on: January 24, 2019, 05:56:36 PM »
How can I do this?
I need to create a column where every cell should have a date one month later than the previous. My particular case needs 120 months. So I can enter Jan. 1, 2019 in cell A1 and it should automatically fill in Feb. 1, 2019 in cell A2 and March 1, 2019 in cell A3 and so on for 120 rows.

Thanks.

Offline Zubda

  • DansDeals Copper Elite
  • *
  • Join Date: Apr 2018
  • Posts: 19
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1228 on: January 24, 2019, 06:01:51 PM »
How can I do this?
I need to create a column where every cell should have a date one month later than the previous. My particular case needs 120 months. So I can enter Jan. 1, 2019 in cell A1 and it should automatically fill in Feb. 1, 2019 in cell A2 and March 1, 2019 in cell A3 and so on for 120 rows.

Thanks.
Enter this formula in cell A2 `=DATE(YEAR(A1), MONTH (A1) +1, DAY(A1))`
Abd drag it down

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2810
  • Total likes: 155
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1229 on: January 24, 2019, 06:33:46 PM »
Enter this formula in cell A2 `=DATE(YEAR(A1), MONTH (A1) +1, DAY(A1))`
Abd drag it down
Just use =EDATE(A1,1) and copy down